<=16:00 - 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)

<=16:00

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-03-26 : 13:50:18

I have the following query

select * from stats where (statsdatetime>='2007-03-20 8:00:00' and statsdatetime<='2007-03-26 18:00:00')

this is returning records with a date of 2007-03-24 18:26:06.000

why? how can I get only records between the horus of 8:00 and 18:00?

similarly i also need an oppostite query with all between the horus of 18:00 and 7:59

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-26 : 13:53:42

You do know that that vaue does fall within that range

So you are looking for those times between those days only?

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

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-26 : 13:56:48

select * from stats
where statsdatetime >= '20070320' and statsdatetime < '20070327'
and datepart(hour, statsdatetime) >= 8 and datepart(hour, statsdatetime) < 18

select * from stats
where statsdatetime >= '20070320' and statsdatetime < '20070327'
and datepart(hour, statsdatetime) < 8 and datepart(hour, statsdatetime) >= 18

Peter Larsson
Helsingborg, Sweden

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-26 : 14:00:28

Your query looks for rows that are within a range starting at midnight on 3/20 and ending at 18:00 on 3/26. If you want to limit time of day, you have to specifically look at time of day, like this

select * from stats where (statsdatetime>='2007-03-20 8:00:00' and statsdatetime<='2007-03-26 18:00:00') and datepart(hh, statsdatetime) between 8 and 17

or for the other hours

select * from stats where (statsdatetime>='2007-03-20 8:00:00' and statsdatetime<='2007-03-26 18:00:00') and datepart(hh, statsdatetime) not between 8 and 17

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-03-26 : 14:08:07

thanks... didn't spot that

and datepart(hh, statsdatetime) between 8 and 17

how do I say between 8:00 and 17:59
and does between include the 17:59 minute?

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-03-26 : 15:07:05

thanks for your help - i got that working

now i'm trying to add onto that query

and (DATENAME(dw , statsdatetime)<>'Sunday' Or DATENAME(dw , statsdatetime) <>'Saturday')

this is returning results on saturday or sunday - what am i doing wrong?

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-26 : 15:21:58

Your logic is wrong, that should be
and (DATENAME(dw , statsdatetime)<>'Sunday' and DATENAME(dw , statsdatetime) <>'Saturday')

Or, more simply you could use
and DATEPART(dw , statsdatetime) between 2 and 6

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-03-26 : 15:28:20

thanks for spotting that...:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources