What the format codes mean in SQL 'CONVERT'

kropes2001 · #1 (**permalink**) 08-28-07, 03:14

Aloha !

I am posting this information simply as an FYI. This is in reference to the MS-SQL command "CONVERT"

I spent over 2 hours

screwing around trying to find out different ways of formatting dates from MS-SQL into something that makes sense for what I needed. I googled everything I could think of and found multiple references that said the info is available on MSDN.. but I could not find it. What I did find were thousands of relatively useless references to "format codes" for converting dates, but with no references to what the different format codes would ultimately yield, or what format codes were available to use.

What I ended up doing was writing a small script to generate a list of all of the variations I could find.

Below is the script, and the output that it yielded.
Now, before I get bombarded with "there is a better way" I know there probably is. But this is the way that I needed to do it this particular time. If there are technical errors in my explanation, anyone is welcome to correct them. But after 2 hours of messing with this for what should have been a super simple single .0009 second command, I am just irritated beyond belief that it had to be this complicated to find any useful information on the subject. That is why I am creating this. Hopefully it helps someone else.

The format for the MS SQL CONVERT command is :

CONVERT( length_of_output, date, format_code )

length_of_output : is exactly that . the number of characters that you want returned as your result. If you use a length of 6 you will only see the first 6 characters that are returned. I found the longest valid length to be 28 characters, but I went as high as 128 just for giggles and to see if it revealed any secrets.

date : is a valid date, I used directly the getdate() function

format_code : well.. that's the tricky part. See below.

What I did was ran a script that originally went from 1 to 20,000. It crashed at 15. Apparently the format codes are not totally sequential. So I put in an on error resume next.

What I found is that :
1) the codes are not uninterrupted sequential numbers.
2) the code output repeats every 255
3) negative numbers can be used, but its pointless.
4) useful valid codes are in the ranges of : 0-14, 20-25, 100-114, 120, 121, 126, 130 and 131
5) 0-25 typically represent "short dates" with the year being only 2 digits, but there are exceptions
6) 100 and above always returned a 4 digit year. the exception was 130 and 131, I don't know what it was trying to do.

Here is the script i ran

Code:

<%
on error resume next

for iintCounter = 0 to 256
	SQL = "SELECT CONVERT(CHAR(128), getdate(), " & iintCounter & " ) as TheDate"
	Set rsTheDateFormat = TheDatabase.Execute(SQL)

	response.write SQL & " = " & rsTheDateFormat("TheDate") & "<br>"

	set rsTheDateFormat = nothing
next
%>

and here is the output

Code:

SELECT CONVERT(CHAR(128), getdate(), 0 ) as TheDate = Aug 28 2007 6:46AM 
SELECT CONVERT(CHAR(128), getdate(), 1 ) as TheDate = 08/28/07 
SELECT CONVERT(CHAR(128), getdate(), 2 ) as TheDate = 07.08.28 
SELECT CONVERT(CHAR(128), getdate(), 3 ) as TheDate = 28/08/07 
SELECT CONVERT(CHAR(128), getdate(), 4 ) as TheDate = 28.08.07 
SELECT CONVERT(CHAR(128), getdate(), 5 ) as TheDate = 28-08-07 
SELECT CONVERT(CHAR(128), getdate(), 6 ) as TheDate = 28 Aug 07 
SELECT CONVERT(CHAR(128), getdate(), 7 ) as TheDate = Aug 28, 07 
SELECT CONVERT(CHAR(128), getdate(), 8 ) as TheDate = 06:46:45 
SELECT CONVERT(CHAR(128), getdate(), 9 ) as TheDate = Aug 28 2007 6:46:45:507AM 
SELECT CONVERT(CHAR(128), getdate(), 10 ) as TheDate = 08-28-07 
SELECT CONVERT(CHAR(128), getdate(), 11 ) as TheDate = 07/08/28 
SELECT CONVERT(CHAR(128), getdate(), 12 ) as TheDate = 070828 
SELECT CONVERT(CHAR(128), getdate(), 13 ) as TheDate = 28 Aug 2007 06:46:45:507 
SELECT CONVERT(CHAR(128), getdate(), 14 ) as TheDate = 06:46:45:507 
SELECT CONVERT(CHAR(128), getdate(), 20 ) as TheDate = 2007-08-28 06:46:45 
SELECT CONVERT(CHAR(128), getdate(), 21 ) as TheDate = 2007-08-28 06:46:45.540 
SELECT CONVERT(CHAR(128), getdate(), 22 ) as TheDate = 08/28/07 6:46:45 AM 
SELECT CONVERT(CHAR(128), getdate(), 23 ) as TheDate = 2007-08-28 
SELECT CONVERT(CHAR(128), getdate(), 24 ) as TheDate = 06:46:45 
SELECT CONVERT(CHAR(128), getdate(), 25 ) as TheDate = 2007-08-28 06:46:45.540 
SELECT CONVERT(CHAR(128), getdate(), 100 ) as TheDate = Aug 28 2007 6:46AM 
SELECT CONVERT(CHAR(128), getdate(), 101 ) as TheDate = 08/28/2007 
SELECT CONVERT(CHAR(128), getdate(), 102 ) as TheDate = 2007.08.28 
SELECT CONVERT(CHAR(128), getdate(), 103 ) as TheDate = 28/08/2007 
SELECT CONVERT(CHAR(128), getdate(), 104 ) as TheDate = 28.08.2007 
SELECT CONVERT(CHAR(128), getdate(), 105 ) as TheDate = 28-08-2007 
SELECT CONVERT(CHAR(128), getdate(), 106 ) as TheDate = 28 Aug 2007 
SELECT CONVERT(CHAR(128), getdate(), 107 ) as TheDate = Aug 28, 2007 
SELECT CONVERT(CHAR(128), getdate(), 108 ) as TheDate = 06:46:45 
SELECT CONVERT(CHAR(128), getdate(), 109 ) as TheDate = Aug 28 2007 6:46:45:913AM 
SELECT CONVERT(CHAR(128), getdate(), 110 ) as TheDate = 08-28-2007 
SELECT CONVERT(CHAR(128), getdate(), 111 ) as TheDate = 2007/08/28 
SELECT CONVERT(CHAR(128), getdate(), 112 ) as TheDate = 20070828 
SELECT CONVERT(CHAR(128), getdate(), 113 ) as TheDate = 28 Aug 2007 06:46:45:930 
SELECT CONVERT(CHAR(128), getdate(), 114 ) as TheDate = 06:46:45:930 
SELECT CONVERT(CHAR(128), getdate(), 120 ) as TheDate = 2007-08-28 06:46:45 
SELECT CONVERT(CHAR(128), getdate(), 121 ) as TheDate = 2007-08-28 06:46:45.943 
SELECT CONVERT(CHAR(128), getdate(), 126 ) as TheDate = 2007-08-28T06:46:45.990 
SELECT CONVERT(CHAR(128), getdate(), 130 ) as TheDate = 15 ????? 1428 6:46:46:040AM 
SELECT CONVERT(CHAR(128), getdate(), 131 ) as TheDate = 15/08/1428 6:46:46:040AM 
SELECT CONVERT(CHAR(128), getdate(), 256 ) as TheDate = Aug 28 2007 6:46AM

gvee · #2 (**permalink**) 08-28-07, 03:31

Quote:

Originally Posted by kropes2001

I spent over 2 hours

screwing around trying to find out different ways of formatting dates from MS-SQL into something that makes sense for what I needed

Aloha!

My only grumble with this post is:
Formatting is a presentation layer issue

r937 · #3 (**permalink**) 08-28-07, 05:52

what in the world were you doing googling, instead of just looking it up in Books OnLine (BOL)?

if for some reason you don't have BOL on your own computer, it's also available online

see CAST and CONVERT

Diabolic · #4 (**permalink**) 08-28-07, 08:48

Quote:

Originally Posted by georgev

Formatting is a presentation layer issue

On the spot

blindman · #5 (**permalink**) 08-28-07, 08:50

To be fair to Kropes, Microsoft in its infinite wisdom only lists the CONVERT function under "CAST AND CONVERT". It does not have an entry of its own, making it slightly more difficult to find...but this is still one of the funnier posts I have seen.

kropes2001 · #6 (**permalink**) 09-03-07, 00:42

yes, typically formatting is a presentation layer thing. however i needed to do something that was never going to be displayed, all internal to the database instead. but thanks for the feedback !

jezemine · #7 (**permalink**) 09-03-07, 12:01

it's really not that hard to find:

http://www.google.com/search?q=convert+sql