Select where datecolumn = today

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select where datecolumn = today

Author

Topic

DaveBF
Yak Posting Veteran

89 Posts

Posted - 2014-11-13 : 12:51:48

I have a table with a column of type date. I want to select all records where the date column = today.

I tried

Select * from myTable where DateSent = GetDate()

but it returns no records.

Do I really have to use this slow method?

select datesent from Messages where convert(varchar,DateSent,103) = CONVERT(varchar, GetDate(), 103)

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-13 : 12:55:27

Getdate() returns the current date and timestamp. Do you have rows in your table that match that exactly? (probably not). So try something like this:


...where GetDate() >= DateSent and GetDate() < dateadd(day, 1, DateSent)

Kristen
Test

22859 Posts

Posted - 2014-11-14 : 08:15:41

If DateSent only contains Date values (with no Time component) then:


Select * from myTable where DateSent = CONVERT(date, GetDate())

If DateSent does contain a time element then I personally wouldn't use dateadd(day, 1, DateSent) as it will usually prevent a suitable index being used to optimise the query, and instead I would do:


Select * from myTable 
where DateSent >= DATEADD(Day, DATEDIFF(Day, 0, GetDate()), 0)
AND DateSent <  DATEADD(Day, DATEDIFF(Day, 0, GetDate()), 1)

The formula probably looks complex, but it is all based on integer arithmetic so is more efficient than, for example, converting dates to strings for comparison.

It might be fine to change the range test as follows, but I haven't checked what the efficiency of the CASTing is:


Select * from myTable 
where DateSent >= CONVERT(Date, GetDate())
AND DateSent <  CONVERT(Date, GetDate()+1)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources