** Converting SQL datetime to 'MORNING', 'AFTERNOON', 'EVENING'

dunkin1969
dunkin1969 used Ask the Experts™
on
Hello,
My data tables store datetimes as '2010-01-21 08:00:45.553'

How can I convert it to say MORNING? or AFTERNOON, or EVENING?

MORNING = 8am to 1:59pm
AFTERNOON = 2pm to 4:59m
EVENING = 5pm to 9pm

Thanks !!!




Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Consultant
Top Expert 2009
Commented:
Hello dunkin1969,

SELECT CASE WHEN CAST( CONVERT(CHAR(2), dt, 108) AS INT) <14 THEN 'Morninig'
            WHEN CAST( CONVERT(CHAR(2), dt, 108) AS INT) <17 THEN 'Afternoon'
            WHEN CAST( CONVERT(CHAR(2), dt, 108) AS INT) <21 THEN 'Evening' ELSE 'Night' END

Regards,

Aneesh

Author

Commented:
Thanks aneeshattingal !
Just curious, if 14 is 2pm, then what is 230pm ?
AneeshDatabase Consultant
Top Expert 2009

Commented:
>Just curious, if 14 is 2pm, then what is 230pm
2pm -> 14 (in 24 hr format )
2.30 ->14.30

Author

Commented:
Thanks !!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial