CODEWITHSUNDEEP
sqlsql-server
Vian Ojeda Garcia
Vian Ojeda Garcia

Reputation: 867

SQL Can't convert to time format computed ss to HH:MM:SS

I can't convert to computed ss to hh:mm:ss to time even though it is formatted as time.This is the query I have. Is there a work around here?

SELECT RIGHT('0' + CAST(263827 / 3600 AS VARCHAR),2) + ':' + 
       RIGHT('0' + CAST((263827 / 60) % 60 AS VARCHAR),2) + ':' + 
       RIGHT('0' + CAST(263827 % 60 AS VARCHAR),2)  [Released]

I tried using CONVERT(time, DATEADD(ms, SUM(aa.Released) * 1000, 0)) but does not return the correct output.

Edit 1: Current output is 73:17:07 but I cant convert it to time format when I use the CONVERT function the error says

Conversion failed when converting date and/or time from character string.

Upvotes: 0

Views: 338

Answers (2)

Caius Jard
Caius Jard

Reputation: 74660

If I was going to turn 263827 into hours minutes and seconds, I’d use dateadd

SELECT FORMAT(DATEADD(SECOND, 263827, '1970-01-01'), 'HH:mm:ss')

Pre 2012 versions of sqlserver don’t have format:

SELECT CONVERT(VARCHAR, DATEADD(SECOND, 263827, '1970-01-01'), 8)

Theres no particular reason I chose 1st jan 1970, any date with a time of midnight would work, but this date is the unix epoch so as a technique it can be used for turning unix time (INT64 number of seconds since jan 1, 1970) into a usable DATETIME..

Note that this approach doesn’t work for seconds over 86400 because it is limited to 24 hours before it rolls over to the next day. You could FORMAT to add "day of year" if you wanted e.g. up to "365d 23:59:59" of duration.

To turn a number of seconds into hundreds of hours, do it mathematically:

SELECT CONCAT(263827/3600, FORMAT(DATEADD(SECOND, 263827, '1970-01-01'), ':mm:ss'))

This is the simplest (shortest code) way I can think of without getting into lots of divide and mod operations (messy)

Upvotes: 3

PSK
PSK

Reputation: 17943

The upper limit of time datatype is 23:59:59, you can convert any varchar to time if it equal or below 23:59:59.

In your case 73:17:07 is beyond the limit of time datatype so you will get the casting error.

Upvotes: 1

Related Questions