>= not working correctly with GetDate()

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

>= not working correctly with GetDate()

Author

Topic

Lorna70
Starting Member

19 Posts

Posted - 2010-03-12 : 04:29:30

I have a SELECT sproc to return all events either today or in the future. Can someone please offer any ideas as to why the following does NOT return records with today's date (only tomorrow's etc.)??

SELECT * FROM vEventsList
WHERE Venue_Id = 1
AND Date_From >= GETDATE()
ORDER BY DATE_FROM

Many thanks
Lorna

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-03-12 : 04:41:04

GETDATE() returns the current date and time which is why you will only get data that has a DATE_FROM that is after the current time of day. If you want all records from the beginning of the day (00:00:00) the do like this:

SELECT * FROM vEventsList
WHERE Venue_Id = 1
AND Date_From >= DATEADD(Day, DATEDIFF(Day, 0, GetDate()), 0)
ORDER BY DATE_FROM

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-03-12 : 04:42:10

Run this to see the difference between the two:

SELECT GETDATE(), DATEADD(Day, DATEDIFF(Day, 0, GetDate()), 0)

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources