January 28, 2011 at 11:41 am
Hi,
I have a date field named MyDate datatype datetime
e.g =2010-06-23 00:00:00.000
in my query I want to get results since the last 24 hours.
I.e From 1/28/2011 to 1/29/2011
When I hard code the date as mentioned above in the query below i get results
SELECt * from mytable name
WHERE MyDate ='1/28/2011
AND MyDate <= '1/29/2011'
But when I use the query below, no data is returned.
SELECt * from mytable name
WHERE MyDate=DATEADD(dd, DATEDIFF(dd, GETDATE(),GETDATE()-1), GETDATE())
AND MyDate <=DATEADD(dd, DATEDIFF(dd, GETDATE(),GETDATE()+1), GETDATE())
Please help!
January 28, 2011 at 11:49 am
I think the first line of the where clause should probably be using >= rather than = as the conditions as posted are mutually exclusive...
Mike
January 28, 2011 at 11:52 am
select
*
from
MyTable
where
--Equal to or greater than start of yesterday
MyDate >= dateadd(dd, datediff(dd, 0,getdate())-1,0)and
--Less than start of yesterday
MyDate < dateadd(dd, datediff(dd, 0,getdate()),0)
January 28, 2011 at 12:05 pm
Mike : It was a typo. I actually meant that.
Michael: Thanks.It worked!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply