integer to 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

SQL Server 2000 Forums

Transact-SQL (2000)

integer to Time

Author

Topic

Gili
Starting Member

42 Posts

Posted - 2008-01-27 : 08:08:20

Hi,

I have a table that have a integer field that represent the time

i want to convert it to time field.

in my table 09:50 is look like this 950.
any ideas??

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-27 : 09:25:07

[code]DECLARE @time int

SELECT @time = 950
SELECT DATEADD(minute, @time % 100, DATEADD(hour, @time / 100, 0))[/code]

KH
[spoiler]Time is always against us[/spoiler]

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-27 : 09:32:22

Do you want to store the "converted" value in the database or simply display the formatted value to the user?

If you want to store it:
There is not [Time] datatype. I think you choices would be a [datetime] using any date as the date component: '1900-01-01 09:50:00.000' or [varchar] which would make the value useless for anything but presenting.

(khtan provided a solution for converting to datetime)

Is the time based on a 24 hour clock? so 1:00 PM is 1300?

If you want to just display it to the user as a formatted time value then you should use the frontend application for that.

Now that I've preached the "what you should do"...
How about:


declare @i int, @time varchar(4)
set @i = 950

set @time = convert(varchar, @i)
select reverse(stuff(reverse(@time), 3, 0, ':'))

output:
----- 
9:50

Be One with the Optimizer
TG

Gili
Starting Member

42 Posts

Posted - 2008-01-28 : 09:23:04

Thanks for your answer you've help me alot!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources