troubleshooting Question

Creating a shift schedule from Datetime column

Avatar of Fred
FredFlag for United States of America asked on
Microsoft SQL ServerSQL
3 Comments1 Solution31 ViewsLast Modified:
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,
                  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

      CONVERT(varchar(50), Create_Date,24),
                  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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 3 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros