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?
alanneese58Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LowfatspreadCommented:
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
KarinLoosCommented:
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
alanneese58Author Commented:
KarinLoos,

Can you remove the date in your example?  I just need the time displayed.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

KarinLoosCommented:
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
alanneese58Author Commented:
Your solution is not taking into account the minutes.  2330 should return "11:30 PM".
0
KarinLoosCommented:
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
alanneese58Author Commented:
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
KarinLoosCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LowfatspreadCommented:
did my solution return an error?
0
alanneese58Author Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.