May 12, 2006 at 6:43 am
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
May 12, 2006 at 7:34 am
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?
May 12, 2006 at 7:43 am
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).
May 12, 2006 at 7:56 am
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
May 12, 2006 at 8:00 am
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