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

• jarid.lawson

  SSCrazy

  Points: 2953

  More actions

  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]

• SQLRNNR

  SSC Guru

  Points: 281334

  More actions

  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

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  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)

• Lowell

  SSC Guru

  Points: 323518

  More actions

  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!

• jarid.lawson

  SSCrazy

  Points: 2953

  More actions

  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]

• veronica.travaini

  SSC Enthusiast

  Points: 104

  More actions

  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.

• Lowell

  SSC Guru

  Points: 323518

  More actions

  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!

• veronica.travaini

  SSC Enthusiast

  Points: 104

  More actions

  January 17, 2014 at 7:18 am

  #1681061

  🙂

  thanks a lot!

  Happy weekend!

  Verónica.

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  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