SQL Server Date formatting

SQL date format 0 or 100 (*) default value mon dd yyyy hh:miam (or PM) 1 101 US mm/dd/yyyy 2 102 ANSI   YY.MM.DD 3 103 UK/France Dd/mm/yy 4 104 Germany dd.mm.yy 5 105 Italy Dd-mm-yy 6 106   -DD Mon yy 7 107-mon dd, yy 8 108-hh:mm:ss-9 or 109 (*) Default value + Ms Mon DD yyyy hh:mi:ss:mmmAM (or PM) 10 110 United States MM-DD-YY 11 111 Japan YY/MM/DD 12 1    ISO yymmdd-13 or 113 (*) European default + MS DD Mon yyyy hh:mm:ss:mmm (24h) 14 114-      Hh:mi:ss:mmm (24h)-20 or (*) ODBC specification YYYY-MM-DD HH:MM:SS[.FFF]-21 or 121 (*)   ODBC specification (with milliseconds) Yyyy-mm-dd Hh:mm:ss[.fff]-126 (* * *) ISO8601 yyyy-mm-dd Thh:mm:ss:mmm (without spaces)- 130* Kuwait DD Mon yyyy hh:mi:ss:mmmam-131* Kuwait dd/mm/yy Hh:mi:ss:mmmAM SQL Server a very powerful day Period Format Function Select CONVERT (varchar (+), GETDATE (), 0): 2006 10:57amselect CONVERT (varchar), GETDATE (), 1): 05/16/06select CONVERT (varchar (10 0), GETDATE (), 2): 06.05.16Select convert (varchar), GETDATE (), 3): 16/05/06select convert (varchar), GETDATE (), 4  ): 16.05.06Select convert (varchar), GETDATE (), 5): 16-05-06select CONVERT (varchar), GETDATE (), 6): 06Select CONVERT (varchar), GETDATE (), 7): 06Select convert (varchar), GETDATE (), 8): 10:57:46select CONVERT (varchar (+), GETDATE (), 9): 2006 10:57:46:827amselect CONVERT (varchar), GETDATE (): 05-16-06select convert (varcha R (+), GETDATE (), one): 06/05/16select convert (varchar), GETDATE (), 060516Select convert (varchar), GETDATE (): 2006 10:57:46:937select CONVERT (varchar (+), GETDATE (), +): 10:57:46:967select convert (varchar), GET DATE (), (): 2006-05-16 10:57:47select convert (varchar), GETDATE (), +): 2006-05-16 10:57:47.157select Convert ( varchar (+), GETDATE (), 22): 05/16/06 10: 57:47 amselect CONVERT (varchar), GETDATE (), max): 2006-05-16select convert (varchar), GETDATE (), 24): 10:57:47select CONVERT (varchar), GETDATE (), (+): 2006-05-16 10:57:47.250select convert (varchar), GETDATE (), : 2006 10:57amselect CONVERT (varchar (1), GETDATE (), 101): 05/16/2006select CONVERT (varchar), GETDATE (), : 2006.05.16Select CONVERT (varchar), GETDATE (), 103): 16/05/2006select convert (varchar), GETDATE (), 104): 16.05.2006Select CONVERT (varchar), GETDATE (), max.: 16-05-2006select convert (varchar), GETDATE (), 106): 16 05 2006Select CONVERT (varchar), GETDATE (), 107): 2006Select convert (varchar, GETDATE (), 108): 10:57:49select CONVERT (varchar), GETDATE (), 109): 2006 10:57:49:437amselect CONVERT (varchar), GETDATE (), : 05-16-2006select CONVERT (varchar), GETDATE (), 111): 2006/05/16select CONVERT (varchar), GETDATE (), 112): 20060516Select CONVERT (varchar), GETDATE (), 113): 16 05 2006 10:57:49:513Select CONVERT (varchar), GETDATE (), 10:57:49:547select CONVERT (varchar), GETDATE (), 120): 2006-05-16 10:57:49select CONVERT (varchar), GETDATE (), 121): 2006-05-16 10:57:49.700select convert (varchar), GETDATE (), 126): 2006-05-16t10:57:49.827select CONVERT (varchar), GETDATE (), ():???? ?????? 1427 10:57:49:907amselect CONVERT (varchar), GETDATE (), 131): 18/04/1427 10:57:49:920am Common: Select CONVERT (varchar ( (+), GETDATE (), 8): 10:57:46select convert (varchar), GETDATE (): 10:57:47select convert (varchar), GETDATE ( ), 108): 10:57:49select CONVERT (varchar), GETDATE (), 060516Select convert (varchar), GETDATE (), 23): 2006-05 -16 usage of CONVERT conversion function in SQL How to convert://////////////////////////////////////////////////////////////////////////// Format: CONVERT (Data_type,e-xpression[,style]) Description: This style is typically in the time type (Datetime,smalldatetime) and the string type (nchar, Nvarchar,char,varchar) are only used when converting to each other. Example: Select convert (varchar (+), GETDATE (), 101) Now results are now---------------------------------------09/15/2001///////////////////////////////////////////////////////////////////////// The meaning of the style number when converting time is as follows--------------------------------------------------------------------------------- ----------------Style (2-bit year) | Style (4-bit for year) | Input and output formats--------------------------------------------------------------------------------------------------| 0 or 100 | Mon dd yyyy hh:miam (or PM)----------------------------------------------------------------------------------------- --------1 | 101 | Mm/dd/yy-------------------------------------------------------------------------------------------------2 | 102 | YY-MM-DD-------------------------------------------------------------------------------------------------3 | 103 | Dd/mm/yy-------------------------------------------------------------------------------------------------4 | 104 | Dd-mm-yy-------------------------------------------------------------------------------------------------5 | 105 | DD-MM-YY-------------------------------------------------------------------------------------------------6 | 106 | DD Mon yy-------------------------------------------------------------------------------------------------7 | 107 | Mon dd,yy-------------------------------------------------------------------------------------------------8 | 108 | Hh:mm:ss--------------------------------------------------------------------------------------------------| 9 or 109 | Mon dd yyyy Hh:mi:ss:mmmmAM (or PM)--------------------------------------------------------------------------------- ----------------10 | 110 | Mm-dd-yy-------------------------------------------------------------------------------------------------11 | 111 | YY/MM/DD-------------------------------------------------------------------------------------------------12 | 112 | YYMMDD--------------------------------------------------------------------------------------------------| or 113 | DD Mon yyyy hh:mi:ss:mmm (24-hour system)-------------------------------------------------------------------------------------------------14 | 114 | Hh:mi:ss:mmm (24-hour system)------------------------------------------------------------------------------------------- ------- | or 120 | Yyyy-mm-dd Hh:mi:ss (24-hour system)------------------------------------------------------------------------------------- ------------- | or 121 |  Yyyy-mm-dd Hh:mi:ss:mmm (24-hour system)

SQL Server Date formatting