Date comparison

  • Hi - I'm trying to do some date comparisons. Basically I have a start and end date for a record and want to know if the 'start date' >= today's date and 'end date' <= today's. My main problem is trying to ignore the fractional part of 'today's date'. My current code is something like :

    where  ....

    and CONVERT(INT,GETDATE(),112)>=CONVERT(INT,h.advert_from,112)

      and CONVERT(INT,GETDATE(),112)<=CONVERT(INT,h.advert_to,112)

     

    but this doesn't seem to work when you get past noon. (I can easily do this in Oracle SQL using the trunc function.) What's the best way?

    Thanks

  • Unless I'm having a brain fart, it seems like the only way records would be returned under your proposed criteria is if both the start and end date were today, or if the start date was later than your end date. Is that accurate?

  • Your very first observation is correct.

    Answer to your question is no. (Advert_from (say 1-1-2006) is always less than advrt_to (say 2-2-2006).

  • i always use convert(varchar(11),getdate()) to put the value out in a real date format.

    then convert it back to datetime

    hence

    select convert(datetime(convert(nvarchar(11),getdate()))

    returns '12-may-2006 00:00:00'

    MVDBA

  • to get rid of the fractional part of a date (time) i usually cast it to a float and then floor that value.

    example:

     cast(floor(cast(GetDate() as float)) as datetime)

    or in your case

    and cast(floor(cast(GetDate() as float)) as datetime) BETWEEN h.advert_from AND h.advert_to

    That is if advert_from and advert_to are datetime datatypes.

Viewing 5 posts - 1 through 4 (of 4 total)

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