Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

convert military time to standard time Expand / Collapse
Author
Message
Posted Thursday, January 6, 2011 4:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 7:03 AM
Points: 8, Visits: 615
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
Post #1044080
Posted Thursday, January 6, 2011 4:49 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:51 PM
Points: 5,401, Visits: 7,513
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.



- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1044082
Posted Friday, January 7, 2011 6:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 7:03 AM
Points: 8, Visits: 615
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
Post #1044375
Posted Friday, January 7, 2011 11:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:51 PM
Points: 5,401, Visits: 7,513
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.


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?






- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1044587
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse