Converting integer to time

Question

Post reply

Converting integer to time

Atif

Hall of Fame

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

#189682

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 8 (of 8 total)

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

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

Hi there,

Hope this helps ^__^

--131000

--220000

--230000

--500000

DECLARE @Table TABLE(TimeNumber INT)

INSERT INTO @Table

SELECT '131000'

UNION

SELECT '220000'

UNION

SELECT '230000'

UNION

SELECT '500000'

-- VARCHAR FORMAT

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

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

FROM @Table

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

FROM @Table

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

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(LEFT(TimeNumber,2) + ':' + SUBSTRING(CAST(TimeNumber AS VARCHAR(6)),3,2) + ':' + RIGHT(TimeNumber,2) AS VARCHAR(8)),14)

FROM @Table

WHERE TimeNumber<=240000

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

FROM @Table

WHERE TimeNumber<=240000

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

_____________________________________________
[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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

DECLARE @Table TABLE(TimeNumber INT)

INSERT INTO @Table

SELECT '131000'

UNION

SELECT '220000'

UNION

SELECT '230000'

UNION

SELECT '050000'

SELECT LEFT(STUFF(STR(TimeNumber,6),3,0,':'),5)

FROM @Table

SELECT REPLACE(LEFT(STUFF(STR(TimeNumber,6),3,0,':'),5),' ','0')

FROM @Table

And they both handle times less than 12 hours and up to 99 hours, too!

--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)
Intro to Tally Tables and Functions

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 3

I don't know if this is any faster than Jeff's (he being the King of Nasty Fast), but it doesn't use any string manipulation (which I try to avoid if I can), strictly math:

declare @Table table(

TimeNumber int

);

insert @Table( TimeNumber )

select 131234 union all

select 221234 union all

select 231234 union all

select 051234;

select TimeNumber,

TimeNumber / 10000 as Hours,

(TimeNumber % 10000) / 100 as Minutes,

TimeNumber % 100 as Seconds,

DateAdd( s, (TimeNumber / 10000 * 60 * 60) + -- hours to seconds

((TimeNumber % 10000) / 100 * 60) + -- minutes to seconds

(TimeNumber % 100),

0 ) as Time

from @Table;

Extract the time portion from the datetime value whatever way you like best. I added some minutes and seconds for testing. Jeff noticed that 500000 is 50 hours, so 5 hours would have to be 050000. That solved a perplexing problem I was having in getting the right answer. 😛

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

Haven't done a performance comparison, but I wouldn't be surprised if your all math solution were faster than just about any string solution including the one I posted.

--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)
Intro to Tally Tables and Functions

jofa SSC Rookie Points: 38 More actions · Answer 5

The expression

TimeNumber/10000*3600 + (TimeNumber%10000)/100*60 + TimeNumber%100

can be simplified to

TimeNumber - TimeNumber/100*40 - TimeNumber/10000*2400

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 6

jofa (8/27/2008)
The expression
TimeNumber/10000*3600 + (TimeNumber%10000)/100*60 + TimeNumber%100
can be simplified to
TimeNumber - TimeNumber/100*40 - TimeNumber/10000*2400

Actually, the code was "60 * 60" not "3600", meaning 60 minutes per hour times 60 seconds per minute.

I wouldn't suggest simplifying it. For one thing, as these are constant, hard-coded values, the compiled code will end up being exactly the same. For another, I may know what is going on and you may know what is going on, but what about the guy hired a year from now who will be looking at the code for the first time when neither of us is around? As long as it doesn't impact performance, write the code to make that guy's job a little easier.

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms

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

Tomm Carr (8/27/2008)
jofa (8/27/2008)
The expression
TimeNumber/10000*3600 + (TimeNumber%10000)/100*60 + TimeNumber%100
can be simplified to
TimeNumber - TimeNumber/100*40 - TimeNumber/10000*2400
Actually, the code was "60 * 60" not "3600", meaning 60 minutes per hour times 60 seconds per minute.
I wouldn't suggest simplifying it. For one thing, as these are constant, hard-coded values, the compiled code will end up being exactly the same. For another, I may know what is going on and you may know what is going on, but what about the guy hired a year from now who will be looking at the code for the first time when neither of us is around? As long as it doesn't impact performance, write the code to make that guy's job a little easier.

Heh... or use a tremendously underutilized bit of code that starts with "--" 😉

--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)
Intro to Tally Tables and Functions

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 8

Jeff Moden (8/27/2008)
Heh... or use a tremendously underutilized bit of code that starts with "--" 😉

Do I hear an "Amen!"?

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms