Solved

# Converting Military Time to regular time.

Posted on 2004-11-18
1,551 Views
I have a field called "Start_Time" which is an Int.  It holds military time values (1800 for 6:00 PM).  How do I get SQL to display "06:00 PM" instead of "1800" on my reports?
0
Question by:alanneese58

LVL 50

Expert Comment

Select Substring(NormalTime,1,2) + ':' + Substring(normaltime,3,2) + Merid as Time
From (
select  Right('0000'+convert(varchar(4),Start_time -  Case When Start_time > 1200 Then 1200 else 0 end),4)                       as               NormalTime
,' ' + Case When Start_time > 1159 then 'PM' else 'AM' end as Merid
From YourTable
) as X

0

LVL 13

Expert Comment

declare @time int
set @time = 1800
select convert( varchar(20), convert(datetime, '1/1/2000 ' +  convert(varchar(10), @time/100) + ':00' ),100)
i hardcoded the 1st january here, but you could add your own date in there

0

Author Comment

KarinLoos,

Can you remove the date in your example?  I just need the time displayed.
0

LVL 13

Expert Comment

declare @time int
set @time = 2300
select ltrim(right( convert( varchar(20), convert(datetime, '1/1/2000 ' +  convert(varchar(10), @time/100) + ':00' ), 100) ,7))

bearing in mind that you cannot have a time which is 2400 -- in this case the value in your field must be  0
0

Author Comment

Your solution is not taking into account the minutes.  2330 should return "11:30 PM".
0

LVL 13

Expert Comment

yes, ok then use
declare @time int
declare @mytime varchar(10)
set @time = 2330

SEt @mytime =
(CASE
WHEN @time >= 1200
then
REPLACE( convert(varchar(10),   convert( decimal(6,2), ( convert( decimal(6,2), @time) / 100)) ), '.', ':') + 'PM'
else
REPLACE( convert(varchar(10),   convert( decimal(6,2), ( convert( decimal(6,2), @time) / 100)) ), '.', ':') + 'AM'
end)
select @mytime
i used a variables here (ie @mytime and @mytime, but in your query it could look

select  (CASE
WHEN timefieldname  >= 1200
then
REPLACE( convert(varchar(10),   convert( decimal(6,2), ( convert( decimal(6,2), timefieldname ) / 100)) ), '.', ':') + 'PM'
else
REPLACE( convert(varchar(10),   convert( decimal(6,2), ( convert( decimal(6,2), timefieldname ) / 100)) ), '.', ':') + 'AM'
end)  as sometime
from table1

0

Author Comment

One last thing and I think we have it.  You need to subtract the twelve hours from the time if it is PM.  2330 is returning "23:30 PM".
0

LVL 13

Accepted Solution

sorry left out part of the answer in copying,  the data in the time field however in my example requires that you have the time portion in the time... confused so am i lol, what i mean is say your time is 0600:am then the data in the time field must say 600 (ie 6 for hours 00 for the mintues) .. the 2400 still applies though this must be 0

declare @time int
declare @mytime varchar(10)
set @time = 1425

SEt @mytime =
(CASE
WHEN @time >= 1200
then
REPLACE( convert(varchar(10),   convert( decimal(6,2), ( convert( decimal(6,2), @time) / 100)) ), '.', ':')
else
REPLACE( convert(varchar(10),   convert( decimal(6,2), ( convert( decimal(6,2), @time) / 100)) ), '.', ':')
end)
select @mytime
select ltrim(right( convert( varchar(20), convert(datetime, '1/1/2000 ' +  @mytime ), 100) ,7))
0

LVL 50

Expert Comment

did my solution return an error?
0

Author Comment

I didn't understand what merid was.  Plus it would have helped me understand your solution better if you had used my variable names in it also.

Thanks.
0

## Featured Post

### Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Acâ€¦
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.