January 13, 2011 at 8:27 am
I have a shortdatetime field in a table called date.
In my query, I would like to pull only today's records, so in where clause,
I will write something like where date =getdate().
But only need the date part. how can I do this in where clause.
Thanks
January 13, 2011 at 8:59 am
where date = dateadd(dd,datediff(dd,0,getdate()),0)
January 13, 2011 at 9:57 am
thanks,
But I got no records. There are acutally 50 records in the table for today.
The whole query is like below:
SELECT ParentUsername, ParentPassword, ADUsername, ADPassword, ParentEmail, Action, Date
FROM MapperReady
where date = dateadd(dd,datediff(dd,0,getdate()),0)
I tried to use below , it works. but want to find the most efficent way for the query
SELECT ParentUsername, ParentPassword, ADUsername, ADPassword, ParentEmail, Action, Date
FROM MapperReady
WHERE (CONVERT(VARCHAR(10), Date, 111) = CONVERT(VARCHAR(10), GETDATE(), 111))
January 13, 2011 at 10:03 am
Anna_SQL (1/13/2011)
thanks,But I got no records. There are acutally 50 records in the table for today.
The whole query is like below:
SELECT ParentUsername, ParentPassword, ADUsername, ADPassword, ParentEmail, Action, Date
FROM MapperReady
where date = dateadd(dd,datediff(dd,0,getdate()),0)
I tried to use below , it works. but want to find the most efficent way for the query
SELECT ParentUsername, ParentPassword, ADUsername, ADPassword, ParentEmail, Action, Date
FROM MapperReady
WHERE (CONVERT(VARCHAR(10), Date, 111) = CONVERT(VARCHAR(10), GETDATE(), 111))
You said you wanted the date without the time, so I assumed that your dates are also stored that way. Obviously, that is not the case.
where
-- Greater then or equal today at midnight
date >= dateadd(dd,datediff(dd,0,getdate()),0)and
-- Before tomorrow at midnight
date < dateadd(dd,datediff(dd,0,getdate())+1,0)
January 13, 2011 at 10:08 am
Thank you!
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply