Converting an Integer to a Datetime

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)

Converting an Integer to a Datetime

Author

Topic

Malks_m99
Starting Member

2 Posts

Posted - 2004-06-17 : 14:26:17

I have a column that is storing the time as an integer.

e.g. 420 would represent 04:20

Does anyone know a quick way to convert the 420 value to 04:20 using a select statement?

Any help would be appreciated.

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-17 : 14:52:31

[code]
DECLARE @x int
SELECT @x = 420

SELECT CONVERT(datetime,'1/1/1900 ' + STUFF(RIGHT('0000'+CONVERT(varchar(4),@x),4),3,0,':')+':00')

[/code]

Brett

8-)

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2004-06-17 : 16:09:49

Is SELECT Stuff(Str(420, 4), 3, 0, ':') close enough?

-PatP

Malks_m99
Starting Member

2 Posts

Posted - 2004-06-17 : 16:29:51

Brett thanks a lot! I used the following:-

select STUFF(RIGHT('0000'+CONVERT(varchar(4),AVIEW_SITE.DEPARTURE_TRANSIT_TIME),4),3,0,':')+':00'
from aview_site

Just needed the result set to display the value as '04:20:00'

Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources