How to Get SQL to Change Date is Time Goes Past Midnight.

Question

How to Get SQL to Change Date is Time Goes Past Midnight.

hazeleyre_23

SSC-Addicted

Points: 449
More actions
December 10, 2015 at 5:00 am

#399449

Hi,
How do I get the date to change if the time someone works goes past midnight.
i.e if someone works 21:00 - 06:00.
I have a roster date column, start shift column and end shift column.:crazy:
Thank you

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247180 More actions · Answer 1

hazeleyre_23 (12/10/2015)
Hi,
How do I get the date to change if the time someone works goes past midnight.
i.e if someone works 21:00 - 06:00.
I have a roster date column, start shift column and end shift column.:crazy:
Thank you

Which date are you referring to?

Can you simply add 1 to this 'date' if the end shift is earlier than the start shift?

Lowell SSC Guru Points: 323546 More actions · Answer 2

if StartShift and EndShift are DateTime data types, you can just use Datediff to get the period of time.

SELECT DateDiff(minute,StartShift,EndShift) from yourtable.

if it's a TIME data type, you have to convert to date time, and if EndShift is less than StartShift, add one day.

if you are storing strings or inteigers representing time(minutes?), you have to jump through even more hoops to convert to datetime.

what datatypes are you actually using? Best practice is always use datetime variables to represent datetimes. otherwise you have a mountain of conversions and extra jumps to go through(leap year, daylight savings, month end dates, etc)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

hazeleyre_23 SSC-Addicted Points: 449 More actions · Answer 3

I am currently using Time datatype which i think is causing the issue.

If i was to change my database to datatime datatype would could i get it to just show time ?

thank you for your help 🙂

Y.B. SSChampion Points: 11652 More actions · Answer 4

hazeleyre_23 (12/10/2015)
I am currently using Time datatype which i think is causing the issue.
If i was to change my database to datatime datatype would could i get it to just show time ?
thank you for your help 🙂

Well how you have it stored and how you "show" it is two different things. However, once you have a datetime field working with the values become much simpler.

Simple Example:

SELECT GETDATE() AS myDateTime, CONVERT(VARCHAR(8),GETDATE(),114) AS myTime

SELECT quote FROM brain WHERE original = 1
0 rows returned

Lowell SSC Guru Points: 323546 More actions · Answer 5

i adapted and created some sample data.

Declare @MyDay datetime = '2015-01-01'

;With MYFirstNames(FName) AS

(

Select 'Leonardo' UNION ALL

Select 'Brad' UNION ALL

Select 'Arnold' UNION ALL

Select 'Mark' UNION ALL

Select 'Matt' UNION ALL

Select 'Bruce'

),

MyLastNames(LName) AS

(

Select 'DeCaprio' UNION ALL

Select 'Pitt' UNION ALL

Select 'Schwarzenegger' UNION ALL

Select 'Wahlberg' UNION ALL

Select 'Damon' UNION ALL

Select 'Willis'

), SomeDataWithTime

AS

(

SELECT TOP 20

A.FName,B.LName ,

CONVERT(time,DATEADD(hh,ABS(CHECKSUM(NEWID())) %24,0)) As StartShift,

CONVERT(time,DATEADD(hh,ABS(CHECKSUM(NEWID())) %24,0)) As EndShift

FROM MYFirstNames A

CROSS JOIN MyLastNames B

)

SELECT StartShift,

EndShift,

CONVERT(datetime,StartShift) StartShiftDT,

CONVERT(datetime,EndShift) StartShiftDT,

CASE

WHEN EndShift <= StartShift --= means they worked 24 hours straight? valid assumption? or did they punch in and out in the same minute?

THEN DATEDIFF(minute, CONVERT(datetime,StartShift),DATEADD(dd,1,CONVERT(datetime,EndShift)))

ELSE DATEDIFF(minute, CONVERT(datetime,StartShift),CONVERT(datetime,EndShift))

END / 60.0 AS ElapsedHours,

* FROM SomeDataWithTime

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

I did something slightly different from Lowell's.

CREATE TABLE #Shifts(

StartShift time,

EndShift time

)

INSERT INTO #Shifts

VALUES( '21:00', '06:00'),

( '06:00', '14:00'),

( '14:00', '21:00');

SELECT *,

(DATEDIFF( MI, CAST( StartShift AS datetime), EndShift) / 60.)

+ CASE WHEN StartShift > EndShift THEN 24 ELSE 0 END AS ElapsedHours

FROM #Shifts

GO

DROP TABLE #Shifts

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Steve Jones - SSC Editor SSC Guru Points: 738612 More actions · Answer 7

If you aren't storing dates, be careful here. I would bet you get a bug at some point when a shift or a schedule goes over 24 hours. I've worked 36 hours before as an hourly person, so be aware of this. Store the date.