select date closest to today

  • hi all,

    I need some help with a stored proc to select the date in table that is closest to today's date.

    The Table:

    field: HiD (primary, not null, int ident)

    field: HDate (datetime)

    So its something like:

    HiD HDate

    1 2/15/2004

    2 4/10/2004

    3 1/29/2002

    How do i find the HiD whose date is closest to today?

    Thank you for any help and advice.

  • How to select in the past (including today)

    SELECT HiD

    FROM   TheTable

    WHERE  HDate = (

                      SELECT MAX(HDate)

                      FROM   TheTable

                      WHERE  HDate < GETDATE()

                   )

    How to select in the past (not including today)

    SELECT HiD

    FROM   TheTable

    WHERE  HDate = (

                      SELECT MAX(HDate)

                      FROM   TheTable

                      WHERE  HDate < DATEADD(dd, -1, GETDATE())

                   )


    N 56°04'39.16"
    E 12°55'05.25"

  • Peter, thank you very much for your help.

    What if the date field includes possible future dates?

    Thanks again.

  • select

    top 1 Hid, HDate

    from

    TheTable

    order

    by abs(HDate - getdate()) asc, Hid desc

    Tim Wilkinson

    "If it doesn't work in practice, you're using the wrong theory"
    - Immanuel Kant

  • This gave an error when I tried it

    My solution

    SELECT TOP 1 Hid, HDate

    FROM

    ORDER BY ABS(DATEDIFF(day,HDate,GETDATE())) ASC

    This will ignore time portion of any dates and will give you arbitary row if there are more than 1 row with equidistant (ie before and after today) difference

    use

    , DATEDIFF(day,HDate,GETDATE()) DESC

    for before today

    or

    --, DATEDIFF(day,GETDATE(),HDate) DESC

    for after today

    other than that you can use Tim's idea and use Hid (ASC or DESC)

    Far away is close at hand in the images of elsewhere.
    Anon.

  • Doesn't matter, since the date picked is the latest date to the date given.

     

    How to select in the past (including today)

    DECLARE @WantedDate DATETIME

    SELECT @WantedDate = 'June 19, 2006'

    SELECT HiD

    FROM   TheTable

    WHERE  HDate = (

                      SELECT MAX(HDate)

                      FROM   TheTable

                      WHERE  HDate < @WantedDate

                   )

    How to select in the past (not including today)

    SELECT @WantedDate = GETDATE()

    SELECT HiD

    FROM   TheTable

    WHERE  HDate = (

                      SELECT MAX(HDate)

                      FROM   TheTable

                      WHERE  HDate < DATEADD(dd, -1, @WantedDate)


    N 56°04'39.16"
    E 12°55'05.25"

  • Implicit conversion from data type datetime to float is not allowed. Use the CONVERT function to run this query.

    I would use something like decimal(20,10):

    select

    top 1 Hid, HDate

    from

    TheTable

    order

    by abs(cast(HDate - getdate() as decimal(20,10))) asc, Hid desc

     

    Tim Wilkinson

    "If it doesn't work in practice, you're using the wrong theory"
    - Immanuel Kant

  • Thank you all for your help and guidance.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply