Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Regular Coder
    Join Date
    Jun 2006
    Location
    UK
    Posts
    922
    Thanks
    302
    Thanked 3 Times in 3 Posts

    Question MySQL: Fetch nearest date

    Hey,

    How do I get the nearest date/time row populated using sql?

    The datatype for this date field is "datetime"

    I tried the folowing, but does not work properly

    Code:
    select YourDateField from yourTable where yourDateField <= date(now())
    Even a search in Google didnt help


    Please help.

    Thanx

  • #2
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,865
    Thanks
    160
    Thanked 2,224 Times in 2,211 Posts
    Try
    Code:
    select YourDateField from yourTable where yourDateField <= date(now()) order by yourDateField DESC limit 1
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • #3
    Regular Coder ralph l mayo's Avatar
    Join Date
    Nov 2005
    Posts
    951
    Thanks
    1
    Thanked 31 Times in 29 Posts
    The previous example returns the closest date to now() that is before or equal to now(), even if there is a closer date after now(). If you want the absolute closest date try something like:

    Code:
    SELECT date FROM table ORDER BY abs(now() - date) LIMIT 1
    Last edited by ralph l mayo; 08-29-2008 at 08:17 AM. Reason: typo


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •