[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1708
  • Last Modified:

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?
0
alanneese58
Asked:
alanneese58
  • 4
  • 4
  • 2
1 Solution
 
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now