CODEWITHSUNDEEP
sqlsql-servertime-format
espresso_coffee
espresso_coffee

Reputation: 6110

Convert time from Military to standard AM/PM Microsoft SQL

What if you just have a time field that is stored as a char and displays in military time, example (1015), and you just want to format it to standard time using sql code. Is there a way to do that?

Update:

To clarify, I am defining "standard time" as meaning 6:30 PM or 4:30AM.

Upvotes: 0

Views: 2326

Answers (3)

JR Price
JR Price

Reputation: 1

Converting Military DateTime to Standard DateTIme

Select Convert(VarChar, Convert(Datetime,'01/12/2024 13:01:7',101),100)

Result:

Jan 12 2024  1:01PM

Upvotes: 0

shawnt00
shawnt00

Reputation: 17915

If you want to minimize the string parsing you could just strip out the hour and minutes fairly readily. This also doesn't assume the length is 4 characters or do any related adjustments to compensate for the lack of a leading zero:

timefromparts(cast(col as int) / 100, cast(col as int) % 100, 0, 0, 0)

Upvotes: 0

GarethD
GarethD

Reputation: 69769

The first step is to insert a colon into your military time:

DECLARE @Time CHAR(4) = '1015';
SELECT  STUFF(@Time, 3, 0, ':');

Which gives 10:15

Then you can convert it to the Time data type:

DECLARE @Time CHAR(4) = '1015';
SELECT  CONVERT(TIME, STUFF(@Time, 3, 0, ':'));

Which gives 10:15:00.0000000

Finally you can convert back to varchar, with the style 100 to get it in a 12 hour format:

DECLARE @Time CHAR(4) = '1015';
SELECT  CONVERT(VARCHAR(7), CONVERT(TIME, STUFF(@Time, 3, 0, ':')), 100);

Which gives 10:15AM

Or

DECLARE @Time CHAR(4) = '2157';
SELECT  CONVERT(VARCHAR(7), CONVERT(TIME, STUFF(@Time, 3, 0, ':')), 100);

Which gives 9:57PM

The standard way to deal with this though would be to store the time using the TIME datatype, and do all formatting in your presentation layer.

Upvotes: 1

Related Questions