Convert Time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Convert Time

Author

Topic

tmaiden
Yak Posting Veteran

86 Posts

Posted - 2006-08-16 : 14:24:16

I have an integer field that has time values.

Here is what I have and what I want.

Example:
0 = "12:00:00 AM"
345 = "12:03:45 AM"
152341 = "3:23:41 PM"

Is this possible.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-08-16 : 14:39:23

here's one way:


declare @t table (time int)
insert into @t
select 0
union all
select 345
union all
select 152341

declare @start datetime
select	@start = '2006-08-16 00:00:00.000'

select	dateadd(s, convert(int, right(tempVal, 2)), 
			dateadd(n, convert(int, substring(tempVal, 3, 2)), 
				dateadd(hh, convert(int, left(tempVal, 2)), @start)))
from
(
select	right(replicate('0',6) + convert(varchar(6), time), 6) as tempVal
from	@t
) t1

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-16 : 18:52:00

[code]
select
X,
[Int to Time] =
dateadd(ss,x%100,dateadd(mi,(x/100)%100,dateadd(hh,x/10000,0)))
FROM
(
-- Test data
select x = 0 union all
select 345 union all
select 152341
) a
[/code]
Results:
[code]
X Int to Time
----------- ------------------------------------------------------
0 1900-01-01 00:00:00.000
345 1900-01-01 00:03:45.000
152341 1900-01-01 15:23:41.000

(3 row(s) affected)

[/code]

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources