Convert Military date to standard

Question

Convert Military date to standard

ramadesai108

SSChampion

Points: 10609
More actions
April 20, 2011 at 10:37 am

#389332

I have a date in the following format, how do I convert it to standard date format:
2011-03-01 13:10:29.817
Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 1

ramadesai108 (4/20/2011)
I have a date in the following format, how do I convert it to standard date format:
2011-03-01 13:10:29.817
Thanks.

What is not "standard" about that date format?

GSquared SSC Guru Points: 260824 More actions · Answer 2

This has options for formatting dates and times: http://msdn.microsoft.com/en-us/library/ms187928.aspx

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

WayneS SSC Guru Points: 95460 More actions · Answer 3

Well, what you have is one of the standard date formats (20/120). I guess what you need is to pick which one you actually want... this might help you out:

SET DATEFORMAT MDY;

DECLARE @date DATETIME;

SET @date = '2011-03-01 13:10:29.817';

;WITH cte (Style) AS

(

SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL

SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL

SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL

SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL

SELECT 100 UNION ALL SELECT 101 UNION ALL SELECT 102 UNION ALL SELECT 103 UNION ALL

SELECT 104 UNION ALL SELECT 105 UNION ALL SELECT 106 UNION ALL SELECT 107 UNION ALL

SELECT 108 UNION ALL SELECT 109 UNION ALL SELECT 110 UNION ALL SELECT 111 UNION ALL

SELECT 112 UNION ALL SELECT 113 UNION ALL SELECT 114 UNION ALL SELECT 120 UNION ALL

SELECT 121 UNION ALL SELECT 126 UNION ALL SELECT 127 UNION ALL SELECT 130 UNION ALL

SELECT 131 UNION ALL SELECT 0

)

SELECT Style, DateOutput = CONVERT(VARCHAR(30), @date, Style)

FROM cte

ORDER BY Style;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Steve Jones - SSC Editor SSC Guru Points: 736059 More actions · Answer 4

the storage format isn't really important. The display is what is important. Display formatting is separate from how the data is stored.

AFAIK, you will have to convert this hours to characters or numbers and then do a CASE to determine if it's > 12. If so, subtract 12 and display that. You'll have to rebuild the string then to show the time.

ramadesai108 SSChampion Points: 10609 More actions · Answer 5

I tried different formats on that site, but it does not work.

Garadin One Orange Chip Points: 29613 More actions · Answer 6

ramadesai108 (4/20/2011)
I tried different formats on that site, but it does not work.

Care to elaborate on that at all?

Did your server just say "No."?

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Steve Jones - SSC Editor SSC Guru Points: 736059 More actions · Answer 7

I tried a dozen or so of the formats, and none of them will convert 13:10 (in a datetime) to 1:10.

The only solutions I've seen were to convert to char, get the hours, do a CASE, and then rebuild a string.

Again, that is for display. What you see in SSMS or what is stored is irrelevant.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 8

What about something along these lines?

SET DATEFORMAT MDY ;

DECLARE @date DATETIME ;

SET @date = '2011-03-01 13:10:29.817' ;

SELECT CONVERT(VARCHAR(30), @date, 101) +

RIGHT(CONVERT(VARCHAR(30), @date, 0), 7) AS [civilian_date]

If you provide an example of what you mean by "standard format" we can better assist.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato