Converting minutes to hh:mm:ss

Question

Post reply

Converting minutes to hh:mm:ss

DaveBriCam

SSCarpal Tunnel

Points: 4298
More actions
December 2, 2020 at 3:14 pm

#3816066

I'm trying to develop a formula to convert minutes (my raw data) to hh:mm:ss format. E.g.: 526.30 minutes = 31,578 seconds which should read 08 hrs. 47 minutes and 8 seconds or 08:46:08 ~ I think (?)

Viewing 11 posts - 1 through 10 (of 10 total)

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

Jeff Moden SSC Guru Points: 1004309 More actions · Answer 1

What do you want to appear for durations that are greater than 24 hours?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 2

Fortunately for this application I don't need to convert any to days as none will exceed about 20 hrs.

This reply was modified 3 years, 11 months ago by DaveBriCam.

Jeff Moden SSC Guru Points: 1004309 More actions · Answer 3

First of all, I recommend NOT formatting data for storage. It's just wrong for so many reasons.

If the data is ultimately bound for storage in a table, then do the following:

 SELECT CONVERT(TIME,DATEADD(ms,526.30*60000,0))

If you truly need it for display purposes, you'll find an overwhelming majority of people that will say that the best place to do such formatting is in the GUI or reporting app that you're using and I totally agree with that.

Sometimes, you've gotta do what you're gotta do (although you still need to follow the idea that it's almost never a good idea to store formatted data except in temporary objects). With that, this will produce the formatted output you desire.

 SELECT CONVERT(CHAR(8),DATEADD(ms,526.30*60000,0),108) --24 hour time or duration

In the code above, the "Date Serial Number" of "0" has been used. "0" is the base date of "1900-01-01' . Multiplying your number by 60,000 converts your number to milliseconds. Adding that number of milliseconds to the base date creates an actual DATETIME datatype containing a date of 1900-01-01 with the correct time in hours, minutes, seconds, a milliseconds with a resolution of 3.3 milliseconds. The CONVERT uses format # 108 to format that date and time in the 24 hour format of hh:mi:ss and dumps that to a CHAR(8) datatype for display purposes.

For more information on formatting dates and times (which, again, is usually a mistake), please see the following MS Documentation. I strongly recommend that you become very familiar with all that's covered in the documentation because it covers a great deal more than just dates and times and you can actually pull off small miracles using CONVERT in conjunction with other functions.

https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15

I also recommend that you become insanely familiar with the other date functions and datatypes because just about everything that people do in SQL Server uses some form of date/time reference. I'll let you Google for those because, unless you've download the help-system (referred to as "Books Online" or just "BOL") to have such documentation available at your finger tips, it's a quintessential skill that folks like us need to become very good at.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18345 More actions · Answer 4

declare @Seconds int = 31578
select @Seconds [@Seconds],
       t.Hours, 
       u.Minutes, 
       v.Seconds,
       CONCAT(RIGHT('0'+CONVERT(varchar,T.Hours),2),':',RIGHT('0'+CONVERT(varchar,U.Minutes),2),':',RIGHT('0'+CONVERT(varchar,V.Seconds),2)) [hh:mm:ss]
  from (values ((@Seconds)/3600)) T(Hours)
 cross apply (values (((@Seconds)%3600)/60)) U(Minutes)
 cross apply (values ((@Seconds%60))) V(Seconds)

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 5

SELECT CONVERT(CHAR(8), DATEADD(SECOND, 60 * 526.30, '00:00:00'), 8)

N 56°04'39.16"
E 12°55'05.25"

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 6

This sql works about this way:

DECLARE @Seconds INT;

SELECT @Seconds = 43052;

SELECT CAST(@Seconds/3600 AS VARCHAR(10))

+ RIGHT(CONVERT(CHAR(8),DATEADD(ss,@Seconds,0),108),6);

I tested it

Jeff Moden SSC Guru Points: 1004309 More actions · Answer 7

I guess I don't understand all the gyrations with separating parts and concatenating them back together for this task in SQL Server. Is there a reason for it? If not, please see my previous post above.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004309 More actions · Answer 8

SwePeso wrote:

SELECT CONVERT(CHAR(8), DATEADD(SECOND, 60 * 526.30, '00:00:00'), 8)

That's another good way (IMHO).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182910 More actions · Answer 9

DaveBriCam wrote:

I'm trying to develop a formula to convert minutes (my raw data) to hh:mm:ss format. E.g.: 526.30 minutes = 31,578 seconds which should read 08 hrs. 47 minutes and 8 seconds or 08:46:08 ~ I think (?)

I must ask why you are not storing the data in the lowest granularity, normally seconds?

😎

SQL Server lags behind when it comes to some temporal concepts such as duration, suggest you work around those shortcomings by using numerical data types and not using any of the existing temporal data types.

ScottPletcher SSC Guru Points: 101087 More actions · Answer 10

SwePeso wrote:

SELECT CONVERT(CHAR(8), DATEADD(SECOND, 60 * 526.30, '00:00:00'), 8)

I prefer this method too, with the minor change that the column name should be used rather than a hard-coded value:

SELECT CONVERT(CHAR(8), DATEADD(SECOND, 60 * column_name, '00:00:00'), 8)

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".