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)
Go to Top of Page

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)

Go to Top of Page
   

- Advertisement -