SQLTeam.com | Weblogs | Forums

Proper way to set time between 7Am and 7PM 24 hour time


I have a datetime column assesseddatetime, now want to use it within a case block.

I am only getting the data for last 1 day, and with in that result, want to set any row that has the time 7am onwards till 7PM as DAY and rest as NIGHT.
Is this a proper way:

  (CASE WHEN CONVERT(datetime, CONVERT(VARCHAR, pas.assessmentdatetime, 108)) BETWEEN CONVERT(datetime, '07:00:00') and CONVERT(datetime,'19:00:00')
 THEN 'Day'
 ELSE 'Night'
   END) as Shift

Thanks a lot for the helpful info.

	when cast(pas.assessmentdatetime as time) 
	    	between '07:00:00' and '19:00:00' then 'Day'
	else 'Night'


To convert datetime to time in 24 hrs format you can try this as well -

DECLARE @p datetime = '2012-12-26 01:00:00.000'

CONVERT (varchar, @p, 121)


There's also:

CASE WHEN HOUR(assesseddatetime) BETWEEN 7 AND 19 THEN 'Day' ELSE 'Night' END


Wouldn't that then designate everything up to 19:59:59:.... as day?