Military 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)

Military Time

Author

Topic

inka
Starting Member

8 Posts

Posted - 2007-05-09 : 13:49:41

Hi All,

I am trying to convert a military time to a standard time. I have tried to use convert function but it isn't working.

Here it is:

convert(varchar(8), sjc.active_start_time, 108)

Here how the military time is stored:

233000
63000
201500
143000

Can anybody tell what I am doing wrong? Thanks.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-09 : 14:43:17

How time is stored? what data type is used for storing it?

DECLARE @T TABLE
(
	d datetime
)

insert @t 
select '23:30:00' union all
select '06:30:00' union all
select '20:15:00' union all
select '14:30:00'

select convert(varchar(8), d, 108) from @t

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Kristen
Test

22859 Posts

Posted - 2007-05-09 : 15:08:09

Isn't it just the leading zeros that have got chopped off? If so right-justifying the value, and then splitting it into its constituent parts should yield the right value.

Kristen

inka
Starting Member

8 Posts

Posted - 2007-05-09 : 15:12:20

The datatype for time is int.

Here is my select statement:
select substring(sj.name,charindex(' ',sj.name,13),len(sj.name)) as 'Database',
sjc.active_start_time as 'Database Backup',
'Hot' as 'Type of Backup'
from
(select name, job_id
from sysjobs
where name like 'DB Backup%') as sj
join
(select name, job_id, active_start_time
from sysjobschedules
where name like 'DBBkup%') as sjc
on sj.job_id = sjc.job_id
where sj.name not like '%Maintenance%'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources