trying to create 3 shift times fro a column Create_Date with datetime datatype
2017-05-08 20:57:00.000
2017-05-09 00:44:00.000
day shift 0700 - 1500 hrs
swing shift 1500- 2300 hrs
Night shift 2300 -0700 hrs.
I tried this option using a case statement that seems not to work.
SELECT Create_Date,
CASE
WHEN Create_Date BETWEEN 07:59:00.000 AND 14:59:00.000 THEN 'FIRST SHIFT'
WHEN Create_Date BETWEEN 15:00.000: AND 22:59.00.000 THEN 'SWING SHIFT'
ELSE 'NIGHT SHIFT'
END ShiftTimes
from dbo.FD_Table
I tried converting the column from date time to a varchar 24 format and tried , it only shows Night shift hours
SELECT
CONVERT(varchar(50), Create_Date,24),
CASE
WHEN Create_Date BETWEEN 0700 AND 1459 THEN 'FIRST SHIFT'
WHEN Create_Date BETWEEN 1500 AND 2259 THEN 'SWING SHIFT'
ELSE 'NIGHT SHIFT'
END ShiftTimes
from dbo.FD_Table
Sample output
20:57:00 NIGHT SHIFT
00:44:00 NIGHT SHIFT
13:17:00 NIGHT SHIFT
14:53:00 NIGHT SHIFT
14:59:00 NIGHT SHIFT
ASKER