comparing date datatype values

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 2000 Forums

SQL Server Development (2000)

comparing date datatype values

Author

Topic

settinUpShop
Starting Member

28 Posts

Posted - 2003-11-14 : 17:50:48

I'm using a dynamic t-sql query and I'd like to use a check in my where clause which will prevent the return of records that have a date older than today's date.

This is what I've been attempting to accomplish this with, but it doens't work.

SELECT Event_Title, Event_Date, Event_Location, Blurb_Text, Sort_Order, Expire_Date, Booth,Link_URL, Link_Text, Event_Status
FROM UpcomingEvents
WHERE Event_Status = 'live' OR Event_Status = 'pending' AND Expire_Date < 11/14/2003
ORDER BY Sort_Order DESC, Expire_Date ASC

What am I doing wrong?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-14 : 17:52:35

AND Expire_Date >= GETDATE()

Do you want the time portion of GETDATE? If not you can remove it using CONVERT.

Tara

settinUpShop
Starting Member

28 Posts

Posted - 2003-11-14 : 18:02:01

awesome thanks Tara :)

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2003-11-16 : 20:24:44

You might want to throw parentheses in there for future reference.

WHERE (Event_Status = 'live' OR Event_Status = 'pending') AND Expire_Date < 11/14/2003
is different from
WHERE Event_Status = 'live' OR (Event_Status = 'pending' AND Expire_Date < 11/14/2003)

Sure you know now that you want the default handling of order between AND and OR, but a year from now when someone else is trying to maintain that routine and make a change, they could get a rude awakening. I like to plan for the future and to make it explicit when I'm mixing my ANDs and ORs.

--------------------------------------------------------
Visit the SQLTeam Weblogs at [url]http://weblogs.sqlteam.com[/url]

settinUpShop
Starting Member

28 Posts

Posted - 2003-11-17 : 19:54:20

thanks for the tip Ajarn. this actually was an issue for me a few days back, because really what I wanted as my where clause was this:

WHERE Expire_Date >= GETDATE() AND (Event_Status = 'live' OR Event_Status = 'pending')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources