Avatar of Fred
FredFlag for United States of America

asked on 

Creating a shift schedule from Datetime column

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
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
Brian Crowe
Avatar of Brian Crowe
Brian Crowe
Flag of United States of America image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Fred
Flag of United States of America image


Running 2016 SQL server , the Create_Date column is in this format 2017-05-08 20:57:00.000 date time 
Avatar of Brian Crowe
Brian Crowe
Flag of United States of America image

FYI, If Create_Date is a DATETIME then it is not stored in that format.  Don't confuse how the data is stored and how it is represented in the presentation layer even if that presentation layer is SSMS.  DATETIME datatype is stored as 2 integers.

That being said if you are running SQL 2016 then the query above should run without issues.
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo