We help IT Professionals succeed at work.

Convert varchar military time to varchar standard time

How would I go about converting the following varchar string to standard time format? (The end result can still be varchar.)

0930 to 9:30 am
Comment
Watch Question

Ephraim WangoyaSoftware Engineer
CERTIFIED EXPERT

Commented:
Try this

declare @s varchar(10)

set @s = '0930'

select LEFT(@s, 2) + ':' + RIGHT(@s, 2) +
		case when cast(LEFT(@s, 2) AS integer) < 12 then 'AM' else 'PM' end

Open in new window

Author

Commented:
That works, except the time is still in military time. If the time is 1345 (1:45 pm) then it shows 13:45 pm.

How can I convert the military time to standard?
Ephraim WangoyaSoftware Engineer
CERTIFIED EXPERT

Commented:
Try this

 
declare @s varchar(10)

set @s = '1345'

select 
	case 
		when cast(LEFT(@s, 2) AS integer) > 12 then
			cast(cast(LEFT(@s, 2) AS integer) - 12 as varchar(2))
		else
			LEFT(@s, 2)
	end
	+ ':' 
	+ RIGHT(@s, 2) +
	case 
		when cast(LEFT(@s, 2) AS integer) < 12 then 
			'AM'
		when cast(LEFT(@s, 2) AS integer) = 24 then 
			'AM' 
		else 'PM'
	end

Open in new window

Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
DECLARE @s varchar(30)
SET @s = '1345'

SELECT RIGHT(CONVERT(varchar(30), CAST(STUFF(@s, 3, 0, ':') AS datetime), 0), 7)

Author

Commented:
Perfect. Thanks.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.