GETDATE Function - SQL Server Forums

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)

GETDATE Function

Author

Topic

oahu9872
Posting Yak Master

112 Posts

Posted - 2005-08-11 : 11:08:02

Hello,

I'm trying to set up a SQL statement where i pull info on a list of events from a database. i only want to show events that happen starting today or in the future. this is what i have, but it shows all events.

is there something wrong with my getDATE function ???

"SELECT DISTINCT states, abbriviation FROM tblStates, tblEvents WHERE tblStates.abbriviation = tblEvents.StateID AND EventDate > GETDate() ORDER BY States"

thanks

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-08-11 : 12:03:35

What datatype is EventDate? Datetime? If not, what do the EventDates look like?

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-08-11 : 12:41:00

GETDATE() returns minutes and seconds and what your statement is doing is saying events starting after this very second in time.

====================================================
Regards,
Sean Roussy

Thank you, drive through

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-08-11 : 12:46:44

Right, but I would have thought that he would be getting fewer results because of the time issue instead of more results. If setting the time to midnight is what you need, then:

... AND EventDate > CONVERT(datetime, CONVERT(char(8), getdate(), 112)) ...

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-08-11 : 13:08:02

depends on the nature of the data. if he wants today forward and all of his data is in there as midnight today and he asks for every greater than this second, whoops where did my data go?

====================================================
Regards,
Sean Roussy

Thank you, drive through

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2005-08-11 : 14:31:01

SELECT DISTINCT states, abbriviation
FROM tblStates, tblEvents
WHERE tblStates.abbriviation = tblEvents.StateID
AND DATEDIFF(DD, GETDATE(), ISNULL(EventDate, GETDATE())) >= 0
ORDER BY States

Aj

Hey, it compiles.

oahu9872
Posting Yak Master

112 Posts

Posted - 2005-08-11 : 16:36:51

Thanks for the help.

It is still showing all events, regardless of if they occur before today or not. I'm not sure what could be wrong with it.

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-11 : 16:42:16

Follow the hint link below so we can help you out. Without enough info, it's not easy.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2005-08-11 : 17:10:20

Might try to change the joins. I think that the way you have them will default to a LEFT join. Might try an INNER join instead.

Aj

Hey, it compiles.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-12 : 02:50:31

If you dont get the expected result, then post sample data and expected result

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources