Convert minutes to HH:MM:SS format

Question

Convert minutes to HH:MM:SS format

pwalter83

SSChampion

Points: 14575
More actions
February 15, 2012 at 4:20 am

#256864

Hi,
I have a requirement to convert minutes to HH:MM:SS format. However, the minutes to be converted are calculated through this query:
(sum(datepart(hour,Timenetin) * 60) + sum(datepart(minute,Timenetin) * 1) + sum(datepart(second,Timenetin)/60)) - (420 * count(case when s.Dayname IN ('Mon', 'Tue','Wed', 'Thu', 'Fri') then 1 else 0 end))
Could someone pls help me in this regard.
Thanks,
Paul

Viewing 15 posts - 1 through 15 (of 16 total)

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

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

pwalter83 (2/15/2012)
Hi,
I have a requirement to convert minutes to HH:MM:SS format. However, the minutes to be converted are calculated through this query:
(sum(datepart(hour,Timenetin) * 60) + sum(datepart(minute,Timenetin) * 1) + sum(datepart(second,Timenetin)/60)) - (420 * count(case when s.Dayname IN ('Mon', 'Tue','Wed', 'Thu', 'Fri') then 1 else 0 end))
Could someone pls help me in this regard.
Thanks,
Paul

You're doing it wrong. . . treat a DATETIME as a DATETIME, a DATE as a DATE and TIME as TIME. Formatting belongs in the presentation layer, not the database.

That being said, you can do it in the database if you must/insist.

First, quick question - what do you want displayed if 61 is the INT returned? 00:61:00 or 01:01:00 ?

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

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

Duplicate post. No replies to this thread please. Direct replies to:http://www.sqlservercentral.com/Forums/Topic1252335-391-1.aspx

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

pwalter83 SSChampion Points: 14575 More actions · Answer 3

GilaMonster (2/15/2012)
Duplicate post. No replies to this thread please. Direct replies to:http://www.sqlservercentral.com/Forums/Topic1252335-391-1.aspx

This is not a duplicate post....its an altogether different query. Read the post Properly first before writing any comments.

If you don't have a solution, at least don't block others from replying.

David Burrows SSC Guru Points: 65142 More actions · Answer 4

CONVERT(char(8),DATEADD(minute,[value],0),114)

Far away is close at hand in the images of elsewhere.
Anon.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

pwalter83 (2/15/2012)
Hi,
I have a requirement to convert minutes to HH:MM:SS format. However, the minutes to be converted are calculated through this query:
(sum(datepart(hour,Timenetin) * 60) + sum(datepart(minute,Timenetin) * 1) + sum(datepart(second,Timenetin)/60)) - (420 * count(case when s.Dayname IN ('Mon', 'Tue','Wed', 'Thu', 'Fri') then 1 else 0 end))
Could someone pls help me in this regard.
Thanks,
Paul

David did it the same way I would have but let me ask... if the number of minutes to be converted 1,441, what do you want displayed?

--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)

GuruGPrasad SSCommitted Points: 1768 More actions · Answer 6

Hi Gail Shaw,

Can I know the reason why my post blocked? I had reply @ "Posted Yesterday @ 10:50:20 AM"

Regards

Guru

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

guruprasad1987 (2/15/2012)
Hi Gail Shaw,
Can I know the reason why my post blocked? I had reply @ "Posted Yesterday @ 10:50:20 AM"
Regards
Guru

Looking back at your previous posts, I don't see such a block. Where is it (URL)?

--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)

GuruGPrasad SSCommitted Points: 1768 More actions · Answer 8

Hi Jeff Moden,

Thank you for your reply. Here is the url http://www.sqlservercentral.com/Forums/Topic1252335-391-1.aspx

Regards

Guru

Ant-Green SSC Guru Points: 113507 More actions · Answer 9

Hi Guru

Thats a completely different thread as you can see in the URL's.

The OP has the same question but two different SQL queries hence the two different posts.

Thanks

Ant

GuruGPrasad SSCommitted Points: 1768 More actions · Answer 10

GuruGPrasad

SSCommitted

Points: 1768

February 16, 2012 at 6:21 am

#1447816

Hi Ant,

Thanks & sorry

Regard

Guru

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 11

guruprasad1987 (2/16/2012)
Hi Jeff Moden,
Thank you for your reply. Here is the url http://www.sqlservercentral.com/Forums/Topic1252335-391-1.aspx
Regards
Guru

I thought you said it was your post that was blocked.

--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)

GuruGPrasad SSCommitted Points: 1768 More actions · Answer 12

Hi Jeff Moden,

Yes my post was blocked.. Its ok no problem

Regards

Guru

Jared SSC Guru Points: 61793 More actions · Answer 13

My guess is that the result for 1441 should be 24:01:00. It seems, looking at this post and the other post by the OP, that this is a duration measurement of something; i.e. Total time spent sleeping during a week. For whatever reason, I am guessing that the analysis should be reported in hours instead of minutes and also not in days. Maybe that is the accepted format in that area (I am saying all of this because of stupid formats accepted when I was studying Neuropsych). Am I right OP?

Jared
CE - Microsoft

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 14

SQLKnowItAll (2/16/2012)
My guess is that the result for 1441 should be 24:01:00. It seems, looking at this post and the other post by the OP, that this is a duration measurement of something; i.e. Total time spent sleeping during a week. For whatever reason, I am guessing that the analysis should be reported in hours instead of minutes and also not in days. Maybe that is the accepted format in that area (I am saying all of this because of stupid formats accepted when I was studying Neuropsych). Am I right OP?

That would be my guess, as well. It would be nice for the OP to come back to confirm because it could be the he really wants 1:00:00:01 (days, hours, minutes, seconds).

My real take on this is that the seconds should simply be added to date "0" and stored as a DATETIME so that it would be easier to manipulate in the future.

--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)