Time formatting - 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)

Time formatting

Author

Topic

kmartin
Starting Member

5 Posts

Posted - 2007-06-01 : 15:17:39

I'm looking to convert an int(4) of a time into a HH:MM:SS string format. Right now the data is in a 24 hour format like this:

132001 (PM) and 20001 (AM)
I would want my final output to look like 1:20:01 and 2:00:01 respectively.

As of now I have the conversion to string done but I can't figure out a way to properly format the time without numbers being misplaced.

Thanks

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-06-01 : 16:05:36

what do you have so far? here's a way to extract each part of the time:


declare @i int
set @i = 123456
select @i, @i/10000 as hours, (@i - 10000 * (@i/10000))/100 as minutes, ((@i - 100 * (@i/100))) as seconds

EDIT: typo

www.elsasoft.org

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-06-02 : 00:52:07

Hi jezemine,

u r qry wont return the desired out put for the input 132001
there is a slight modification,

select @i, case when @i/10000 > 12 then (@i/10000) - 12 else @i/10000 end as 'hour',
right( '0' + cast((@i - 10000 * (@i/10000))/100 as varchar(10)) , 2)as minutes,
right( '0' + cast((@i - 100 * (@i/100)) as varchar(10)), 2)as seconds

Peter.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-06-02 : 01:32:23

yea, well you have to leave something for the OP to do...