Date query not working correctly

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

SQL Server 2005 Forums

Express Edition and Compact Edition (2005)

Date query not working correctly

Author

Topic

ipstrategies
Starting Member

2 Posts

Posted - 2009-09-30 : 12:14:16

Hello I am having a problem getting a date query to return correctly.

Below is the query - stored procedure

SELECT AttractionId, AttractionName, AttractionStartDate, AttractionEndDate
FROM Attractions
WHERE (Active = 'True') AND (CONVERT(varchar, AttractionStartDate, 101) >= GETDATE())
ORDER BY AttractionEndDate

It will not display any attractions for current date. I thought the >= would bring back any date = or greater then today?

Thanks for the help

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-09-30 : 13:19:18

WHERE Active = 'True' AND AttractionStartDate >= DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0)

ipstrategies
Starting Member

2 Posts

Posted - 2009-09-30 : 13:32:45

Thank you very much. Worked great, can you give me a quick reason to where I went wrong?

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-10-01 : 06:07:46

You got your original results as you were stripping the time off AttractionStartDate and comparing against GETDATE() which returns the time as well. ie It would only work as you expected at 00h00.

Also:
1. You should avoid functions on columns as it stops the optimizer considering any index on that column. (Look up SARGable.)
2. Using DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0) gives the current day at 00h00. Also these functions are only called once, not once per row.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources