Can't convert minutes to hours after 24 hours

Question

Can't convert minutes to hours after 24 hours

gailcole

Old Hand

Points: 340
More actions
July 11, 2017 at 2:22 pm

#324509

I am calculating time worked based on minutes worked. A resident will work more that 24 hours. When they work more that 24 hours I get back 1 minute.
DECLARE @intTime AS INT = 1435
SELECT DATEADD(minute, @intTime, '')
result = 1900-01-01 23:55:00.000
DECLARE @intTime AS INT = 1441
SELECT DATEADD(minute, @intTime, '')
result = 1900-01-02 00:01:00.000
it should be 1900-01-02 24:01:00.000
Does anyone know what function I can use to calculate this

Viewing 7 posts - 1 through 6 (of 6 total)

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

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

gailcole - Tuesday, July 11, 2017 2:22 PM
I am calculating time worked based on minutes worked. A resident will work more that 24 hours. When they work more that 24 hours I get back 1 minute.
DECLARE @intTime AS INT = 1435
SELECT DATEADD(minute, @intTime, '')
result = 1900-01-01 23:55:00.000
DECLARE @intTime AS INT = 1441
SELECT DATEADD(minute, @intTime, '')
result = 1900-01-02 00:01:00.000
it should be 1900-01-02 24:01:00.000
Does anyone know what function I can use to calculate this

24:01 is not a valid time. Are you really sure that that is what you want?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

gailcole - Tuesday, July 11, 2017 2:22 PM
it should be 1900-01-02 24:01:00.000

That's not a valid time though. A clock's not going to show you 1 minute past 24 o clock, because it doesn't exist.

If you want strange formats like that, more than 24 hours in a day, more than 60 minutes in an hour or things like that, you'll probably have to generate them by hand and do the calculations from first principals.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Joe Torre SSChampion Points: 10248 More actions · Answer 3

Time Worked is a interval, not a time. Use the DateDiff Function. If I start working 10 AM yesterday and stop at 10:01 today:
DECLARE @StartTime datetime = '20170710 10:00', @QuitingTime datetime = '20170711 10:01', @intMinutes int; SELECT DateDiff(mi, @StartTime, @QuitingTime) MinutesWorked;

gailcole Old Hand Points: 340 More actions · Answer 4

I need to format this for the UI in an hours"minutes worked format. I am stripping of the 1900-01-01 piece.
My best solution so far:
DECLARE @intTime2 AS INT = 2945
DECLARE @time AS VARCHAR(50)
SET @time = CASE
                WHEN DATEADD(minute, @intTime2, '') BETWEEN '1900-01-01 00:00:00.000' and '1900-01-02 00:00:00.000'
                    THEN convert(VARCHAR(50),DATEADD(minute, @intTime2, ''),114)
                WHEN DATEADD(minute, @intTime2, '') BETWEEN '1900-01-02 00:00:00.000' and '1900-01-03 00:00:00.000'
                    THEN CAST(CAST(DATEDIFF(hour,'1900-01-02 00:00:00.000' , '1900-01-03 00:00:00.000') AS int)+
                             CAST(SUBSTRING(convert(VARCHAR(50),DATEADD(minute, @intTime2, ''),114),1,2) AS INT) AS VARCHAR(15))+ ':' +
                        RIGHT(convert(VARCHAR(50),DATEADD(minute, @intTime2, ''),114),9)
                WHEN DATEADD(minute, @intTime2, '') BETWEEN '1900-01-03 00:00:00.000' and '1900-01-04 00:00:00.000'
                    THEN CAST(CAST(DATEDIFF(hour,'1900-01-02 00:00:00.000' , '1900-01-03 00:00:00.000') AS int)+
                             CAST(DATEDIFF(hour,'1900-01-03 00:00:00.000' , '1900-01-04 00:00:00.000') AS int)+
                             CAST(SUBSTRING(convert(VARCHAR(50),DATEADD(minute, @intTime2, ''),114),1,2) AS INT) AS VARCHAR(10)) + ':' +
                        RIGHT(convert(VARCHAR(50),DATEADD(minute, @intTime2, ''),114),9)
                END
SELECT @time

Lowell SSC Guru Points: 323518 More actions · Answer 5

you could do it as a string representation like this:
/*--results ElapsedString Days Hours Minutes Seconds Milliseconds 3:03:30:11 3 3 30 11 446 */ WITH MySampleData AS ( SELECT [Days] = datediff(day,0,ET-ST), [Hours] = datepart(Hour,ET-ST), [Minutes] = datepart(Minute,ET-ST), [Seconds] = datepart(Second,ET-ST), [Milliseconds] = datepart(millisecond,ET-ST) from ( select -- Test Data ST = convert(datetime,'2017/07/08 00:35:33.997'), ET = convert(datetime,'2017/07/11 04:05:45.443') ) a

) SELECT CONVERT(VARCHAR,[Days]) + ':' + RIGHT('00' + CONVERT(VARCHAR,[Hours]),2) + ':' + RIGHT('00' + CONVERT(VARCHAR,[Minutes]),2)+ ':' + RIGHT('00' + CONVERT(VARCHAR,[Seconds]),2) AS ElapsedString, * FROM MySampleData

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!

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 6

Check out this article from Itzik Ben-Gan (and the function that he's created)... How to Compute Date and Time Difference in Parts