CODEWITHSUNDEEP
sql-servert-sql
user5582884
user5582884

Reputation: 13

Convert from 4 digit Military time to Standard time

I am using SQL Server 2016 and I'm trying to convert military time to standard time. The military time is a 4 digit integer and I'm trying to get the standard time format (00:00 am/pm).

I ran a simple CASE statement that made it standard but without ':' and 'am/pm'.

CASE
  WHEN  SA_BEGTIME between 0 and 1259 then SA_BEGTIME  
  WHEN SA_BEGTIME between 1300 and 2400 then  SA_BEGTIME - 1200

  ELSE ''

END as Time

Results

How do I convert it so that it is in the right format: '00:00 am/pm'

Thank you!

Upvotes: 1

Views: 8169

Answers (5)

Carol
Carol

Reputation: 1

I needed to convert a datetime field where the time was military to just the time in AM/PM format. This worked beautifully.

Left(convert(varchar(20), cast(MyDateField as time),100),7)

Upvotes: 0

Jaxidian
Jaxidian

Reputation: 13511

I'm using the solution that @BaconBits provided but I had to make a tweak because 2400 was a valid representation but it failed with that code. Here's the tweaked solution:

declare @x int = 2400
select TIMEFROMPARTS((@x / 100) % 24,@x % 100, 0, 0, 0)

Upvotes: 1

Bacon Bits
Bacon Bits

Reputation: 32170

You can split it into parts with integer division and modulo, cast it to a VARCHAR and then you can convert it to a TIME:

declare @x int = 109

select cast(cast(@x / 100 as varchar(2)) + ':' + cast(@x % 100 as varchar(2)) as time)

Or you can use the new TIMEFROMPARTS() function (SQL Server 2012+):

declare @x int = 109

select TIMEFROMPARTS(@x / 100,@x % 100, 0, 0, 0)

You can then format it however you'd like.

Upvotes: 4

datagod
datagod

Reputation: 1051

Assuming your data is stored as an integer, and also assuming there is not invalid time stored (i.e. values above 2400 or below 0) you can use the following:

declare @TheTime int = 900
select right(convert(varchar(20),
         cast(stuff(right('0000' + convert(varchar(4),@TheTime),4),3,0,':')
        as datetime),100),7)
-------
 9:00AM

Sorry for the density of the solution. This is what I did:

  1. Convert @TheTime to varchar(4)
  2. Add a string of zeros at the front
  3. Take the rightmost 4 characters from this new string
  4. Stuff a colon sign in the middle of this new string
  5. Cast the string as datetime
  6. Convert back to string using 100 as the style indicator to get AM/PM
  7. Get the right most 7 characters of the string.

I am sure there are more elegant ways, but this one works for me quite well.

Upvotes: 1

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

You can use convert to get n appropriate presentation of time.

declare @mt varchar(4) = '1500'

select convert(varchar, cast(left(@mt, 2) + ':' + right(@mt, 2) as time), 0)

Upvotes: -1

Related Questions