Link to home
Start Free TrialLog in
Avatar of alanneese58
alanneese58

asked on

Converting Military Time to regular time.

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?
Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image

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


Avatar of KarinLoos
KarinLoos

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

Avatar of alanneese58

ASKER

KarinLoos,

Can you remove the date in your example?  I just need the time displayed.
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
Your solution is not taking into account the minutes.  2330 should return "11:30 PM".
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  

 
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".
ASKER CERTIFIED SOLUTION
Avatar of KarinLoos
KarinLoos

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
did my solution return an error?
Lowfatspread,

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.