How to convert INTEGER to TIME

Question

Post reply

How to convert INTEGER to TIME

pshvets

SSCommitted

Points: 1850
More actions
February 13, 2009 at 11:54 am

#200266

Hello,
I have a column which is integer data type and it represents number of seconds.
I need to convert it into MINUTES:SECONDS format.
Conversion of int into time is prohibited in SQL server.
MOD function doesn't seem to work in my SQL Server 2008 installation either
Could someone suggest how to do that?
Thanks,
Pit.

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

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

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

SELECT

CONVERT(VARCHAR,[Column] / 60) + ':' + RIGHT('00' + CONVERT(VARCHAR,[Column] % 60),2)

FROM

[Table]

gyessql Ten Centuries Points: 1097 More actions · Answer 2

declare @time int

set @time = 70

select cast((@time / 60) as varchar(2)) + ':' + cast((@time % 60) as varchar(2))

pshvets SSCommitted Points: 1850 More actions · Answer 3

Thanks a lot Michael!

It works fine.

Just curious, why MOD doesn't work in SQL 2008. Can I set some parameter to turn it on?

Thanks,

Pit.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 4

There is no MOD function in SQL Server; % is the modulus operator

select Mod_Test = 100 % 60, VER = left(@@version,60)

Results:

Mod_Test VER

----------- ------------------------------------------------------------

40 Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)

(1 row(s) affected)

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 5

DECLARE @seconds int;

SET @seconds = 14400;

SELECT DATEADD(second, @seconds, '20090101')

,CONVERT(char(5), DATEADD(second, @seconds, '20090101'), 108);

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

pshvets SSCommitted Points: 1850 More actions · Answer 6

Michael,

It's not I am arguing, but why SQL Server Online Help lists MOD function?

Pit.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

pshvets (2/13/2009)
Michael,
It's not I am arguing, but why SQL Server Online Help lists MOD function?
Pit.

You sure you looking only at SQL server and not a visual basic reference as well?

Keep in mind that the MOD operator would work in SSIS or SSRS (since in some spots they use VB syntax and operators), but not in T-SQL

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

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

pshvets (2/13/2009)
Hello,
I have a column which is integer data type and it represents number of seconds.
I need to convert it into MINUTES:SECONDS format.
Conversion of int into time is prohibited in SQL server.
MOD function doesn't seem to work in my SQL Server 2008 installation either
Could someone suggest how to do that?
Thanks,
Pit.

Do you have an upper limit of 3599 seconds on this? What do you want to display if you hit 3600 seconds? What do you want to display if you hit 86400 seconds?

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

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 9

Jeff Moden (2/13/2009)
Do you have an upper limit of 3599 seconds on this? What do you want to display if you hit 3600 seconds? What do you want to display if you hit 86400 seconds?

[font="Verdana"]Those are the reasons I would recommend using the dateadd() approach (in another example above.)[/font]

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 10

DateAdd or not, the op hasn't identified what happens when you exceed 59 minutes and 59 seconds. If the duration in seconds equals two days, should the answer still be expressed in minutes and seconds or not?

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

pshvets SSCommitted Points: 1850 More actions · Answer 11

I don't expect my data to exceed 20-30 min. I wouldn't need any special handling for such cases.

Thanks,

Pit.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 12

Then, the easiest way to handle things like leading zeros and the like, is to let SQL Server do it all for you...

SELECT RIGHT(CONVERT(CHAR(8),DATEADD(s,Seconds,0),108),5)

FROM (--==== Your table name would go here.... this is just for demo...

SELECT 1 AS Seconds UNION ALL

SELECT 10 UNION ALL

SELECT 100 UNION ALL

SELECT 1000 UNION ALL

SELECT 2000 UNION ALL

SELECT 3000 UNION ALL

SELECT 3599

) yourtable

Micheal Earl's method is probably better, though... if it does go over 3559 seconds, his will show minutes over 60. Mine is probably a bit faster because it has one less CONVERT, but it will not accomodate times over 59:59 and it will not warn you.

--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: 1004339 More actions · Answer 13

So... did any of this solve your problem or not?

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

pshvets SSCommitted Points: 1850 More actions · Answer 14

Sorry for not posting back...

Yes, suggestion by Michael Earl had worked for me!

Thanks a lot for all help!

It is very usefull portal and forum.

Pit.