January 6, 2011 at 4:43 pm
need a way (SQL) to convert military time to a standard time format. I almost got it but I'm having a little trouble.
1338= 01:38:00 PM
1112= 11:12:00 AM
1135= 11:35:00 AM
1149= 11:49:00 PM
2014= 08:14:00 PM
22:35= 10:35:00 PM
Here's what I have so far; any help would do
select mycol, Right(Convert(VarChar(30),
Convert(DateTime, Stuff(Stuff(Right(Replicate('0', 6) + Convert(VarChar(8), mycol), 6), 3,0, ':'), 6, 0, ':')), 100), 7) as timevalues from miltarytbl
January 6, 2011 at 4:49 pm
I think it's because of where you're including the replicated 0's.
This will work:
DECLARE @MilDate VARCHAR(20)
SET @MilDate = '13:38'
SET @MilDAte = REPLACE( @MilDate, ':', '')
SELECT @MilDate
SET @MilDate = LEFT( @MilDate + REPLICATE( '0', 6), 6)
SELECT @MilDate AS Timevalues
SET @MilDate = STUFF( STUFF( @MilDate, 3, 0, ':'), 6, 0, ':')
SELECT @MilDate
SELECT CONVERT( VARCHAR(25), CONVERT( DATETIME, @MilDATE), 100)
EDIT: I lied, close, but jumped the gun. Formatting is off when I apply it to convert to style 100. Back in a minute or two.
Heh, beat myself up with a typo, whoops. All's good.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 7, 2011 at 6:54 am
I'm not that familiar with functions is there a way to do this with SQL? Here's what i revise from your example.
select mycol, LEFT( mycol+ REPLICATE( '0', 6), 6)+STUFF( STUFF( mycol, 3, 0, ':'), 6, 0, ':')+ CONVERT( VARCHAR(25), CONVERT( TIME, mycol), 100)from militarytbl
January 7, 2011 at 11:28 am
wlblee38 (1/7/2011)
I'm not that familiar with functions is there a way to do this with SQL? Here's what i revise from your example.
:ermm: That was SQL. Proc based, but SQL. If you're talking about inlineSQL for a table statement...
select mycol, LEFT( mycol+ REPLICATE( '0', 6), 6)+STUFF( STUFF( mycol, 3, 0, ':'), 6, 0, ':')+ CONVERT( VARCHAR(25), CONVERT( TIME, mycol), 100)from militarytbl
This will have some trouble running, primarily because you broke the layering. I broke out each step above to help you see how each piece modified the data. I'll get back to you about the final, all in one, it'll take some manipulation to combine properly... and if you're using the TIME datatype as I see above, are you on SQL 2k8 or are you actually on SQL 2k?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply