Converting integer to time

Question

Post reply

Converting integer to time

Atif

Hall of Fame

Points: 3578
More actions
August 23, 2008 at 3:08 am

#191474

AOA
I have some integer values like
131000
220000
230000
500000
.
.
How to convert these integers to time as
13:10
22:00
23:00
05:00
.
.
Regards
DBDigger Microsoft Data Platform Consultancy.

Viewing 9 posts - 1 through 9 (of 9 total)

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

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

Duplicate post.

Replies to the following thread please:

http://www.sqlservercentral.com/Forums/Topic557734-338-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

Carl Federl One Orange Chip Points: 25384 More actions · Answer 2

For table msdb.dbo.sysjobhistory, since some @#*& idiot at MS has columns run_date , run_time and run_duration using the integer datatype with an internal format of YYYYMMDD or HHMMSS, I have written two user defined functions to convert to the more appropriate data types.

In your case, as SQL Server 2005 does not support a time data type, do you want to convert to a character datatype with colon separator ?

declare @hhmmss integer

set @hhmmss = 123456

select STUFF(STUFF(RIGHT('000000' + CAST ( @hhmmss as VARCHAR(6 ) ) ,6),5,0,':'),3,0,':')

For duration, you need to decide the precision needed and the below udf uses a precision of seconds. Alternatives are hours, minutes, milliseconds or nanoseconds.

create FUNCTION dbo.HHMMSS_to_Seconds

( @HHMMSSinteger

)

RETURNS integer

AS

BEGIN

RETURN(3600 * ( @HHMMSS / 10000 )

+ 60 * ( ( @HHMMSS

- ( ( @HHMMSS / 10000 ) * 10000 )

- (@HHMMSS % 100 ) ) / 100

)

+(@HHMMSS % 100 )

)

END

create FUNCTION dbo.YYMMDD_HHMMSS_to_TS

( @YYYYMMDDinteger

, @HHMMSSinteger

)

RETURNS datetime

AS

BEGIN

RETURN(( CAST (

( CAST ( @YYYYMMDD as VARCHAR(8) ) + ' '

+ STUFF(STUFF(RIGHT('000000'

+ CAST ( @HHMMSS as VARCHAR(6 ) ) ,6),5,0,':'),3,0,':')

) as datetime ))

)

END

SQL = Scarcely Qualifies as a Language

Atif Hall of Fame Points: 3578 More actions · Answer 3

Thanks a lot

DBDigger Microsoft Data Platform Consultancy.

Quatrei.X SSCrazy Points: 2697 More actions · Answer 4

PLEASE DON'T DOUBLE POST...

I thought I replied to this earlier but my reply suddenly disappeared...

Ok, here's a simpler way... only one select statement for this...

by the way... the reason why my code is long because I included different formats that you might like, hope it helps also

--13100 <----- I intenionally changed the value for testing ^__^

--220000

--230000

--500000

DECLARE @Table TABLE(TimeNumber INT)

INSERT INTO @Table

SELECT '13100'

UNION

SELECT '220000'

UNION

SELECT '230000'

UNION

SELECT '500000'

SELECT RIGHT('00'+CAST(TimeNumber AS VARCHAR(MAX)),6)

FROM @Table

-- VARCHAR FORMAT

-- this is recomended for one that records the timer types which exceed 24 hours

-- SAMPLE #1

SELECT LEFT(RIGHT('00'+CAST(TimeNumber AS VARCHAR(MAX)),6),2) + ':' + SUBSTRING(RIGHT('00'+CAST(TimeNumber AS VARCHAR(MAX)),6),3,2) + ':' + RIGHT(TimeNumber,2)

FROM @Table

-- SAMPLE #2

SELECT LEFT(RIGHT('00'+CAST(TimeNumber AS VARCHAR(MAX)),6),2) + ':' + SUBSTRING(RIGHT('00'+CAST(TimeNumber AS VARCHAR(MAX)),6),3,2)

FROM @Table

-- DATETIME FORMAT

-- this is NOT applicable for one that records the timer types which exceed 24 hours because its in Datetime format

--SELECT CONVERT(DATETIME,CAST('11:00' AS VARCHAR(MAX)),8)

-- SAMPLE #3

SELECT CONVERT(DATETIME,CAST(LEFT(RIGHT('00'+CAST(TimeNumber AS VARCHAR(MAX)),6),2) + ':' + SUBSTRING(RIGHT('00'+CAST(TimeNumber AS VARCHAR(MAX)),6),3,2) + ':' + RIGHT(TimeNumber,2)AS VARCHAR(8)),14)

FROM @Table

WHERE TimeNumber<=240000

-- SAMPLE #4

SELECT CONVERT(DATETIME,CAST(LEFT(RIGHT('00'+CAST(TimeNumber AS VARCHAR(MAX)),6),2) + ':' + SUBSTRING(RIGHT('00'+CAST(TimeNumber AS VARCHAR(MAX)),6),3,2)AS VARCHAR(8)),14)

FROM @Table

WHERE TimeNumber<=240000

Hope it still helps...

_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!

"Press any key...
Where the heck is the any key?
hmmm... Let's see... there's ESC, CTRL, Page Up...
but no any key"
- Homer Simpson

Quatrei.X SSCrazy Points: 2697 More actions · Answer 5

Hey Carl Federl nice one... your code is way better than mine... i realy like it... forgot about that one...

Hey AShehzad ... Uhmm I have a question... what time do you want to get with this integer

707070?

71:11:10 or an error? since minutes and seconds only has 60

_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!

"Press any key...
Where the heck is the any key?
hmmm... Let's see... there's ESC, CTRL, Page Up...
but no any key"
- Homer Simpson

Atif Hall of Fame Points: 3578 More actions · Answer 6

Quatrei, i was required to convert time from msdb..sysjobschedules where it is in int format. Thanks for all of you

DBDigger Microsoft Data Platform Consultancy.

Carl Federl One Orange Chip Points: 25384 More actions · Answer 7

One needs to be specific when refering to "time" as this can mean either a "point in time" or a "duration of time" An example is a meeting that starts at 3:30 (a "point in time") and runs for 3:30 (a "duration of time" ). Points and Durations are two different datatypes with two different sets of rules.

A description of the ISO standard for international standard for date and time representations can be found on WikiPedia at http://en.wikipedia.org/wiki/ISO_8601

The "point in time" can have a range of "00:00:00" to "24:00:00" but the standard display format of "duration of time" is entirely different and is specified as "P#Y#M#DT#H#M#S" where # is a number. For example, "P3Y6M4DT12H30M5S" represents a duration of

"three years, six months, four days, twelve hours, thirty minutes, and five seconds".

Here is a UDF that converts from time duration in seconds to the ISO display format:

CREATE FUNCTION dbo.DurationSeconds_to_ISO

( @Duration_Secondsinteger

)

RETURNS varchar(255)

/*

ISO STANDARD 8601 is used for output format

-- http://en.wikipedia.org/wiki/ISO_8601

-- "P3Y6M4DT12H30M5S" represents a duration of

-- "three years, six months, four days, twelve hours, thirty minutes, and five seconds".

*/

AS

BEGIN

/*

Testing:

declare @Duration_Secondsinteger

set@Duration_Seconds

= ( 24 * 60 * 60) * 3 -- 3 days

+ ( 4 * 60 * 60 )-- 4 hours

+ ( 5 * 60 )-- 5 minutes

+ 6-- 6 seconds

SELECTdbo.DurationSeconds_to_ISO(@Duration_Seconds)

*/

declare@secondsinteger

,@minutesinteger

,@hoursinteger

,@daysinteger

--Seconds in a time unit

,@MinuteSecondsinteger

,@HourSecondsinteger

,@DaySecondsinteger

set@MinuteSeconds= 60

set@HourSeconds= (60 * 60 )

set@DaySeconds= (24 * 60 * 60)

set@seconds= @Duration_Seconds % @MinuteSeconds

set@days= @Duration_Seconds / @DaySeconds

set@minutes= ( ( @Duration_Seconds - @seconds ) / @MinuteSeconds ) % @MinuteSeconds

set@hours= ( @Duration_Seconds - @seconds - ( @minutes * @MinuteSeconds ) - ( @days * @DaySeconds)) / @HourSeconds

RETURN( 'P'

+ cast(@days as varchar(4)) + 'DT'

+ CAST(@hours as varchar(2)) + 'H'

+ CAST(@minutes as varchar(2)) + 'M'

+ CAST(@secondsas varchar(2)) + 'S'

)

end

SQL = Scarcely Qualifies as a Language

Quatrei.X SSCrazy Points: 2697 More actions · Answer 8

:D:P:D:P:D:P

LOL!

Hehehe Thanks!

We'll I hope my post helped in string manipulation hehehehe

By the way, those codes were very useful... I copied it, might be useful in future projects.

_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!

"Press any key...
Where the heck is the any key?
hmmm... Let's see... there's ESC, CTRL, Page Up...
but no any key"
- Homer Simpson

Converting integer to time

Cookies on SQLServerCentral