Converting Minutes to HH:MM:SS

Question

Post reply

Converting Minutes to HH:MM:SS

situlshah

Grasshopper

Points: 23
More actions
February 27, 2009 at 4:05 pm

#131325

I have minutes coming in as varchar. I can change that to int or float if that is necessary
How to I convert that to HH:MM:SS format
Any help would be really appreciated
thanks

Viewing 5 posts - 1 through 4 (of 4 total)

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

Amit Lohia SSCertifiable Points: 6104 More actions · Answer 1

DECLARE @a int

SET @a=125

SELECT RIGHT('00' + CAST(@A/60 AS varchar(2)),2) + ':' + RIGHT('00' + CAST(@A - (@A/60) * 60 AS varchar(2)),2) + ':00'

Kindest Regards,

Amit Lohia

situlshah Grasshopper Points: 23 More actions · Answer 2

Thanks Amit

Does this work for negative minutes also

eg if I have -130 can I get -02:10:00 ?

How can I modify this query for that?

I really appreciate your help on this

Amit Lohia SSCertifiable Points: 6104 More actions · Answer 3

There may be other ways.

DECLARE @a int

SET @a=-111

SELECT CASE @a WHEN ABS(@A) THEN RIGHT('00' + CAST(@A/60 AS varchar(2)),2) + ':' + RIGHT('00' + CAST(@A - (@A/60) * 60 AS varchar(2)),2) + ':00'

ELSE

'-' + RIGHT('00' + CAST(ABS(@A)/60 AS varchar(2)),2) + ':' + RIGHT('00' + CAST(ABS(@A) - (ABS(@A)/60) * 60 AS varchar(2)),2) + ':00'

END;

Kindest Regards,

Amit Lohia

Lowell SSC Guru Points: 323518 More actions · Answer 4

here's a step by step, using the DATEADD function, which is hte better way to tackle this i think:

as far as positive or negative, you'd want to handle that as a CASE statement, other wise you get like 9:55 pm instead of 2:05

[font="Courier New"]DECLARE @A INT

SET @A=125

--we just need a day with no time

SELECT DATEADD(d, 0 ,CONVERT(VARCHAR,GETDATE(),101)) --this is todays date with no time

--2009-02-27 00:00:00.000

SELECT DATEADD(minute,@A,DATEADD(d, 0 ,CONVERT(VARCHAR,GETDATE(),101)))--now we have the date with the time component

--2009-02-27 02:05:00.000

--now we just want the time:

SELECT CONVERT(VARCHAR,DATEADD(minute,@A,DATEADD(d, 0 ,CONVERT(VARCHAR,GETDATE(),101))),108)

--02:05:00

--but i want positive or negative>?

SET @A=- 125

SELECT CASE

WHEN -125 < 0

THEN '-' ELSE ''

END + CONVERT(VARCHAR,DATEADD(minute,ABS(@A),DATEADD(d, 0 ,CONVERT(VARCHAR,GETDATE(),101))),108) AS THEVALUE

-- -02:05:00

[/font]

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!