Converting Integer to Time 2

[wildek]

Hi,

I'm trying to convert a date field that is set up as an integer to a regular time format. For instance, the current output for the field is 66600 which is really 6:30Pm. How can I make it output the time as 6:30 PM?

Thanks,
Ken

 

[sunila7]

Hi,

Try this... is this wht u r looking for


select right(convert(varchar(30), dateadd(ss,INTFLD,'1/1/1900') ,109) ,14) FROM TBL


Sunil

 

[ClaireHCM]

Try this ,it may look ugly,but it does work

select cast(left(convert(decimal(10,2),times)/3600,2)%12 as varchar)+':'+
left(cast(round(cast(right(convert(decimal(10,2),times)/3600,8)as money)*60,0) as varchar),2)
+' PM'
as float from e

e is tablename
times is the column which has value like 66600

 

[ClayG]

66600 is number of seconds since midnight - right ?

Select Convert( Datetime, 66600 *1.0/3600/24)

This gives you a date 1-Jan-1900, the time part is what you want. (*1.0 makes sure its float)

For just the time part:

Select Convert( varchar(8), Convert( Datetime, 66600 *1.0/3600/24), 108)

 

[wildek]

Thanks for all the replies! It works now.

Thanks again,
Ken