CODEWITHSUNDEEP
sqldatabasesql-server-2016
jr7138
jr7138

Reputation: 47

Case Statement of time field

Using this case statement, and could use some help with the first condition:

 case
    when convert(varchar, tblrelease.releaseddt, 108) BETWEEN '22:00:00' AND '06:00:00' then '10PM to 6AM' -- 1st
    when convert(varchar, tblrelease.releaseddt, 108) BETWEEN '06:00:00' AND '14:00:00' then '6AM to 2PM' -- 2nd
    when convert(varchar, tblrelease.releaseddt, 108) BETWEEN '14:00:00' AND '22:00:00' then '2PM to 10PM' -- 3rd 

        else 'UNDEFINED'
end AS [Shift],

The first case condition doesn't return '10PM to 6AM'. It should return the value as stated. Please see the sample table results:

enter image description here

tblrelease.releaseddt = (smalldatetime, null)

Upvotes: 0

Views: 1363

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Based on the syntax, I'm going to assume that the you are using SQL Server. The simplest way to handle this code is probably to focus on the hours:

(case when datepart(hour, tblrelease.releaseddt) between 6 and 13
      then '6AM to 2PM'
      when datepart(hour, tblrelease.releaseddt) between 14 and 20
      then '2PM to 10PM'
      when tblrelease.releaseddt is not null          
      then '10PM to 6AM'
      else 'UNDEFINED'
 end) as Shift,

You can also use time:

(case when convert(time, tblrelease.releaseddt) >= '06:00:00' and
           convert(time, tblrelease.releaseddt) < '14:00:00' 
       then '6AM to 2PM' -- 2nd
       when convert(time, tblrelease.releaseddt) >= '14:00:00' and
            convert(time, tblrelease.releaseddt) < '22:00:00' 
      when tblrelease.releaseddt is not null          
      then '10PM to 6AM'
      else 'UNDEFINED'
 end) as Shift,

This gets around the midnight issue by looking at the other two shifts first. Note that there is no reason to convert to a string to accomplish this. And BETWEEN is not recommended. Your code is ambiguous on where the boundaries should go.

Upvotes: 2

4EACH
4EACH

Reputation: 2197

Separate the query before and after midnight

 case
    when (convert(varchar, tblrelease.releaseddt, 108) BETWEEN '22:00:00' AND '23:59:59') OR 
         (convert(varchar, tblrelease.releaseddt, 108) BETWEEN '00:00:00' AND '06:00:00') then '10PM to 6AM' -- 1st
    when convert(varchar, tblrelease.releaseddt, 108) BETWEEN '06:00:00' AND '14:00:00' then '6AM to 2PM' -- 2nd
    when convert(varchar, tblrelease.releaseddt, 108) BETWEEN '14:00:00' AND '22:00:00' then '2PM to 10PM' -- 3rd 

        else 'UNDEFINED'
end AS [Shift],

Upvotes: 0

Related Questions