Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

Military time as regular time

I have a table I'm able to select on only
It has 2 columns and an id
the columns are in military time...but are varchar(200)

I need to do a select and output as regular time with a space and AM or PM

Example

ckin
1600

needs to be output at 4:00 PM
0
lrbrister
Asked:
lrbrister
  • 3
1 Solution
 
MNelson831Commented:
Declare @Datafield as varchar(50)

Set @DataField = '1600'

select
      Case
            when left(@DataField,2) - 12 < 0 then Left(@DataField,2)
            Else Left(@DataField,2)
      End + ':' + right(@DataField,2)
0
 
MNelson831Commented:
OOps

Try this instead

Declare @Datafield as varchar(50)

Set @DataField = '1600'

select
      Case
            when left(@DataField,2) - 12 < 0 then Left(@DataField,2)
            Else Left(@DataField,2) -12
      End + ':' + right(@DataField,2)
0
 
MNelson831Commented:
Guess I should read slower... missed the AM/PM part


Declare @Datafield as varchar(50)

Set @DataField = '1600'

select
      Case
            when left(@DataField,2) - 12 < 0 then Left(@DataField,2)  + ':' + right(@DataField,2) + ' PM'
            Else Left(@DataField,2) -12 + ':' + right(@DataField,2) + ' AM'
      End as NewTimeValue
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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