Results 1 to 12 of 12
  1. #1
    Join Date
    Nov 2005
    Location
    Honolulu HI
    Posts
    119

    Unanswered: What the format codes mean in SQL 'CONVERT'

    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
    .
    .
    http://www.HawaiianHope.org
    Providing Technology services to non profit orgs, Homeless Shelters, Food Pantries, Clean And Sober Houses and more.
    To date we have given away over 900 free computers !
    __________________________
    caeli enarrant gloriam Dei !

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jul 2007
    Posts
    96
    Quote Originally Posted by georgev
    Formatting is a presentation layer issue
    On the spot

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Nov 2005
    Location
    Honolulu HI
    Posts
    119
    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 !
    .
    .
    http://www.HawaiianHope.org
    Providing Technology services to non profit orgs, Homeless Shelters, Food Pantries, Clean And Sober Houses and more.
    To date we have given away over 900 free computers !
    __________________________
    caeli enarrant gloriam Dei !

  7. #7
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    it's really not that hard to find:

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

  8. #8
    Join Date
    Jul 2012
    Posts
    1
    Quote Originally Posted by r937 View Post
    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

    Why is everyone hating on Kropes? I'm very new to SQL and his post was far more helpful than anything anyone else has linked to. If everyone dropped their egos and got over the "Hate the Noob" phenomena, you would see that his post has almost twice as many entries as your precious MSDN library.

    I think this post is great. You saved me a lot of time, Kropes!

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "hate the noob" -- lol, good one

    tell me again how you're going to ignore the official manual, bookmark this five-year-old thread, and rely on it for accuracy in years to come

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I wrote a script that shows all possible permutations of a SELECT statement, but it is still running after four years...

    ...but as soon as it finishes I'll post the results online and I'm sure they will be helpful reference for everyone.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Still running? Maybe you need to consider Moore's law as it applies to Slacking.

  12. #12
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    Hi Christo4bx and welcome to the forums,

    For a first post you did well (even if it was for a rather old topic) - gave us a new term (Hate the Noob) and maybe even a new acronym (HtN). . .

    It is usually best to reply to currently active topics

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •