Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to convert INTEGER to TIME Expand / Collapse
Author
Message
Posted Friday, February 13, 2009 11:54 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, February 18, 2011 10:54 AM
Points: 54, Visits: 154
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.
Post #656902
Posted Friday, February 13, 2009 12:00 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,661, Visits: 23,078
SELECT
CONVERT(VARCHAR,[Column] / 60) + ':' + RIGHT('00' + CONVERT(VARCHAR,[Column] % 60),2)
FROM
[Table]


Post #656909
Posted Friday, February 13, 2009 12:13 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 27, 2011 12:59 AM
Points: 61, Visits: 898

declare @time int
set @time = 70

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


Post #656914
Posted Friday, February 13, 2009 12:21 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, February 18, 2011 10:54 AM
Points: 54, Visits: 154
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.
Post #656921
Posted Friday, February 13, 2009 12:28 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 11:09 PM
Points: 3,108, Visits: 11,502
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)


Post #656925
Posted Friday, February 13, 2009 12:38 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:31 AM
Points: 4,358, Visits: 9,537
DECLARE @seconds int;
SET @seconds = 14400;

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



Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #656934
Posted Friday, February 13, 2009 1:27 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, February 18, 2011 10:54 AM
Points: 54, Visits: 154
Michael,
It's not I am arguing, but why SQL Server Online Help lists MOD function?

Pit.
Post #656961
Posted Friday, February 13, 2009 2:55 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:22 PM
Points: 7,074, Visits: 15,318
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?
Post #657002
Posted Friday, February 13, 2009 6:48 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:28 PM
Points: 35,263, Visits: 31,750
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #657073
Posted Sunday, February 15, 2009 1:56 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 4:12 PM
Points: 758, Visits: 1,031
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?

Those are the reasons I would recommend using the dateadd() approach (in another example above.)
Post #657392
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse