SmallDateTime - 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 2005 Forums

Transact-SQL (2005)

SmallDateTime

Author

Topic

NeilC
Yak Posting Veteran

55 Posts

Posted - 2008-09-16 : 16:16:59

I cave an events table there with StartTime as 2008-09-16 10:00:00 (SmallDateTime) and I wasnt to write a query to list all the events for a particular day. I'll need two queries to seperate AM and PM because they will be bound to a .net repeater.

I thought I could just do:


Select * from Events where StartTime = '2008-09-16' and datename(hour, StartTime) < 13

but it is not returning data because of the time in the small date time.

Any Ideas?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-16 : 16:30:54

I don't understand why you need two queries.

Select * from Events where StartTime >= '2008-09-16' and StartTime < '2008-09-17'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

NeilC
Yak Posting Veteran

55 Posts

Posted - 2008-09-16 : 16:34:50

One returning AM events and one returning PM events - your query returns everything regardless of AM or PM

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-16 : 16:55:57

And that's the part that I don't understand why you need it that way.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-16 : 22:19:17

obviously your column StartTime contains date & time so "StartTime = '2008-09-16'" will not return anything

Don't understand why do you need 2 query, but here it is

AM query

Select * from Events where StartTime >= '2008-09-16' and StartTime < '2008-09-16 13:00:00'

PM query

Select * from Events where StartTime >= '2008-09-16 13:00:00' and StartTime < '2008-09-17'

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-17 : 02:28:44

quote:
Originally posted by khtan

obviously your column StartTime contains date & time so "StartTime = '2008-09-16'" will not return anything

Don't understand why do you need 2 query, but here it is

AM query
Select * from Events where StartTime >= '2008-09-16' and StartTime < '2008-09-16 13:00:00'

PM query
Select * from Events where StartTime >= '2008-09-16 13:00:00' and StartTime < '2008-09-17'

KH
[spoiler]Time is always against us[/spoiler]

That should be

AM query

Select * from Events where StartTime >= '2008-09-16' and StartTime < '2008-09-16 12:00:00'

PM query

Select * from Events where StartTime >= '2008-09-16 12:00:00' and StartTime < '2008-09-17'

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-17 : 02:36:00

I just following OP's definition of AM < 13

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources