• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1614
  • Last Modified:

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
0
susnewyork
Asked:
susnewyork
  • 2
  • 2
1 Solution
 
Ephraim WangoyaCommented:
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

0
 
susnewyorkAuthor 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?
0
 
Ephraim WangoyaCommented:
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

0
 
Scott PletcherSenior DBACommented:
DECLARE @s varchar(30)
SET @s = '1345'

SELECT RIGHT(CONVERT(varchar(30), CAST(STUFF(@s, 3, 0, ':') AS datetime), 0), 7)
0
 
susnewyorkAuthor Commented:
Perfect. Thanks.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

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