how to classify records based on time intervals

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)

how to classify records based on time intervals

Author

Topic

isaacbs
Starting Member

10 Posts

Posted - 2007-11-26 : 17:05:05

Hi, anybody knows if I have a table with a datetime column in SQL 2005, what would be the SQL statement(s) that would let me classify those records based on the hour

For example if the hour of the datetime column is between
6:30 and 9:00 print 'Morning'
But if its between
20:30 and 23:59 print 'Night'

Tnanks in advance
Isaac

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-11-26 : 17:14:32

A CASE statement should work.

cr8nk
Yak Posting Veteran

77 Posts

Posted - 2007-11-26 : 17:22:22

SET NOCOUNT ON

DECLARE @HOURS CHAR(5)

SELECT @HOURS = SUBSTRING(CONVERT(VARCHAR(30),GETDATE(),13),13,5)

SELECT CASE
WHEN @HOURS BETWEEN '06:30' AND '09:00'
THEN 'MORNING'
WHEN @HOURS BETWEEN '20:30' AND '23:59'
THEN 'NIGHT'
ELSE 'NOT MORNING AND NOT NIGHT'
END as Hours

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources