Post reply

Convert(Varchar,GetDate(),???) - List of Format Codes

  • April 26, 2012 at 10:39 am

    #255437

    I'm posting this for an easy to access code snippet I use on a somewhat regular basis, and also for anyone else who can't remember the difference between formats 5, 101, 131, etc. I tested every number between 0 and 1001 to ensure I covered everything. I hope this helps others as much as I know it will help me.

    --=== Convert Date Format Cheat Sheet

    -- Jarid Lawson

    -- 4/26/2012

    --

    -- This script is for anyone who can't remember the exact format

    -- code in the Convert(Varchar,SomeDate,FormatCode) SQL statement.

    -- This gives every possible date format I found testing every

    -- number between 0 and 1001.

    Select 0 As FormatCode, Convert(Varchar,GetDate(),0) As DateResult Union All

    Select 1, Convert(Varchar,GetDate(),1) Union All

    Select 2, Convert(Varchar,GetDate(),2) Union All

    Select 3, Convert(Varchar,GetDate(),3) Union All

    Select 4, Convert(Varchar,GetDate(),4) Union All

    Select 5, Convert(Varchar,GetDate(),5) Union All

    Select 6, Convert(Varchar,GetDate(),6) Union All

    Select 7, Convert(Varchar,GetDate(),7) Union All

    Select 8, Convert(Varchar,GetDate(),8) Union All

    Select 9, Convert(Varchar,GetDate(),9) Union All

    Select 10, Convert(Varchar,GetDate(),10) Union All

    Select 11, Convert(Varchar,GetDate(),11) Union All

    Select 12, Convert(Varchar,GetDate(),12) Union All

    Select 13, Convert(Varchar,GetDate(),13) Union All

    Select 14, Convert(Varchar,GetDate(),14) Union All

    Select 20, Convert(Varchar,GetDate(),20) Union All

    Select 21, Convert(Varchar,GetDate(),21) Union All

    Select 22, Convert(Varchar,GetDate(),22) Union All

    Select 23, Convert(Varchar,GetDate(),23) Union All

    Select 24, Convert(Varchar,GetDate(),24) Union All

    Select 25, Convert(Varchar,GetDate(),25) Union All

    Select 100, Convert(Varchar,GetDate(),100) Union All

    Select 101, Convert(Varchar,GetDate(),101) Union All

    Select 102, Convert(Varchar,GetDate(),102) Union All

    Select 103, Convert(Varchar,GetDate(),103) Union All

    Select 104, Convert(Varchar,GetDate(),104) Union All

    Select 105, Convert(Varchar,GetDate(),105) Union All

    Select 106, Convert(Varchar,GetDate(),106) Union All

    Select 107, Convert(Varchar,GetDate(),107) Union All

    Select 108, Convert(Varchar,GetDate(),108) Union All

    Select 109, Convert(Varchar,GetDate(),109) Union All

    Select 110, Convert(Varchar,GetDate(),110) Union All

    Select 111, Convert(Varchar,GetDate(),111) Union All

    Select 112, Convert(Varchar,GetDate(),112) Union All

    Select 113, Convert(Varchar,GetDate(),113) Union All

    Select 114, Convert(Varchar,GetDate(),114) Union All

    Select 120, Convert(Varchar,GetDate(),120) Union All

    Select 121, Convert(Varchar,GetDate(),121) Union All

    Select 126, Convert(Varchar,GetDate(),126) Union All

    Select 127, Convert(Varchar,GetDate(),127) Union All

    Select 130, Convert(Varchar,GetDate(),130) Union All

    Select 131, Convert(Varchar,GetDate(),131)

    [font="Arial"]“Any fool can know. The point is to understand.”
    - Albert Einstein

    "DOH!"
    - Homer Simpson[/font]

  • April 26, 2012 at 10:44 am

    #1479908

    very handy.

    Thanks

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • April 26, 2012 at 10:48 am

    #1479911

    130 and 131 should be using NVARCHAR as they may produce Hijri script

    Like this:

    Select 130, Convert(NVarchar(50),GetDate(),130) Union All

    Select 131, Convert(NVarchar(50),GetDate(),131)

  • April 26, 2012 at 10:55 am

    #1479918

    very simiar to mine;

    i put this proc in master, adn whenever i need to refresh myself with date conversions, i just run "sp_dates" in SSMs, to get a quickie preview again:

    CREATE PROCEDURE [dbo].[sp_dates](@date as DATETIME=NULL)

    AS

    BEGIN

    IF @date IS NULL

    SET @date = getdate()

    SELECT CONVERT(VARCHAR,@date,101) AS FormattedDate,'101' AS Code,'SELECT CONVERT(VARCHAR,@date,101)' AS SQL UNION

    SELECT CONVERT(VARCHAR,@date,102) AS FormattedDate,'102' AS Code,'SELECT CONVERT(VARCHAR,@date,102)' AS SQL UNION

    SELECT CONVERT(VARCHAR,@date,103) AS FormattedDate,'103' AS Code,'SELECT CONVERT(VARCHAR,@date,103)' AS SQL UNION

    SELECT CONVERT(VARCHAR,@date,104) AS FormattedDate,'104' AS Code,'SELECT CONVERT(VARCHAR,@date,104)' AS SQL UNION

    SELECT CONVERT(VARCHAR,@date,105) AS FormattedDate,'105' AS Code,'SELECT CONVERT(VARCHAR,@date,105)' AS SQL UNION

    SELECT CONVERT(VARCHAR,@date,106) AS FormattedDate,'106' AS Code,'SELECT CONVERT(VARCHAR,@date,106)' AS SQL UNION

    SELECT CONVERT(VARCHAR,@date,107) AS FormattedDate,'107' AS Code,'SELECT CONVERT(VARCHAR,@date,107)' AS SQL UNION

    SELECT CONVERT(VARCHAR,@date,108) AS FormattedDate,'108' AS Code,'SELECT CONVERT(VARCHAR,@date,108)' AS SQL UNION

    SELECT CONVERT(VARCHAR,@date,109) AS FormattedDate,'109' AS Code,'SELECT CONVERT(VARCHAR,@date,109)' AS SQL UNION

    SELECT CONVERT(VARCHAR,@date,110) AS FormattedDate,'110' AS Code,'SELECT CONVERT(VARCHAR,@date,110)' AS SQL UNION

    SELECT CONVERT(VARCHAR,@date,111) AS FormattedDate,'111' AS Code,'SELECT CONVERT(VARCHAR,@date,111)' AS SQL UNION

    SELECT CONVERT(VARCHAR,@date,112) AS FormattedDate,'112' AS Code,'SELECT CONVERT(VARCHAR,@date,112)' AS SQL UNION

    SELECT CONVERT(VARCHAR,@date,113) AS FormattedDate,'113' AS Code,'SELECT CONVERT(VARCHAR,@date,113)' AS SQL UNION

    SELECT CONVERT(VARCHAR,@date,114) AS FormattedDate,'114' AS Code,'SELECT CONVERT(VARCHAR,@date,114)' AS SQL UNION

    SELECT CONVERT(VARCHAR,@date,120) AS FormattedDate,'120' AS Code,'SELECT CONVERT(VARCHAR,@date,120)' AS SQL UNION

    SELECT CONVERT(VARCHAR,@date,121) AS FormattedDate,'121' AS Code,'SELECT CONVERT(VARCHAR,@date,121)' AS SQL UNION

    SELECT CONVERT(VARCHAR,@date,126) AS FormattedDate,'126' AS Code,'SELECT CONVERT(VARCHAR,@date,126)' AS SQL UNION

    SELECT CONVERT(VARCHAR,@date,130) AS FormattedDate,'130' AS Code,'SELECT CONVERT(VARCHAR,@date,130)' AS SQL UNION

    SELECT CONVERT(VARCHAR,@date,131) AS FormattedDate,'131' AS Code,'SELECT CONVERT(VARCHAR,@date,131)' AS SQL UNION

    SELECT CONVERT(VARCHAR,@date,112) + '-' + CONVERT(VARCHAR,@date,114) AS FormattedDate,'---' AS Code,'SELECT CONVERT(VARCHAR,@date,112) + ''-'' + CONVERT(VARCHAR,@date,114)' AS SQL UNION

    SELECT CONVERT(VARCHAR,@date,112) + '-' + REPLACE(CONVERT(VARCHAR,@date,108),':','') AS FormattedDate,'---' AS Code,'SELECT CONVERT(VARCHAR,@date,112) + ''-'' + REPLACE(CONVERT(VARCHAR,@date,108),'':'','''')' UNION

    SELECT CONVERT(VARCHAR,@date,112) + '-' + REPLACE(CONVERT(VARCHAR,@date,114),':','') AS FormattedDate,'---' AS Code,'SELECT CONVERT(VARCHAR,@date,112) + ''-'' + REPLACE(CONVERT(VARCHAR,@date,114),'':'','''')' AS SQL

    ORDER BY CODE

    END

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • April 26, 2012 at 12:01 pm

    #1479962

    Lowell (4/26/2012)

    very simiar to mine;

    i put this proc in master, adn whenever i need to refresh myself with date conversions, i just run "sp_dates" in SSMs, to get a quickie preview again:

    I didn't think about setting it as a procedure in Master. Good call. To that end there is also a way to set this as a hot key:

    Tools > Options > Keyboard

    I put mine on Ctrl + F1, and just entered (using my procedure name, and without quotes) 'Master.dbo.DateFormats'. Now when I need to see it I just press Ctrl + F1, and boom, there it is. That is handy.

    [font="Arial"]“Any fool can know. The point is to understand.”
    - Albert Einstein

    "DOH!"
    - Homer Simpson[/font]

  • January 17, 2014 at 7:05 am

    #1681055

    Hi everybody,

    Thanks for the snippets, they're very useful!

    I noticed there isn't a CONVERT to only obtain Time with hour + minutes, without the seconds...

    I need to get that since I'm asking for the Time in a reporting services parameter, and it's not comfortable for the user to type this way "HH:mm:ss", but this way is "HH:mm".

    Thanks in advance!

    Best regards,

    Verónica.

  • January 17, 2014 at 7:12 am

    #1681060

    format 108 givesw the time including seconds, includingpreceeding zeros, so you could do a substring/left of that:

    SELECT LEFT(CONVERT(VARCHAR,getdate(),108),5) --09:10

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 17, 2014 at 7:18 am

    #1681061

    🙂

    thanks a lot!

    Happy weekend!

    Verónica.

  • January 17, 2014 at 8:38 am

    #1681099

    At work we usually use 112 or 120 and if I need a different format I usually look for it on BOL. It helps when you're not always working with the same servers.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

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

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