SQL date formatting

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/French dd/mm/yy
4 104 German dd. mm. yy
5 105 Italian DD-mm-yy
6 106-dd mon YY
7 107-mon DD, YY
8 108-hh: mm: SS
-9 or 109 (*) Default Value + millisecond mon dd yyyy hh: MI: SS: mmmam (or pm)
10 110 US mm-dd-yy
11 111 YY/MM/DD in Japan
12 112 ISO yymmdd
-13 or 113 (*) European default value + millisecond dd mon yyyy hh: mm: SS: Mmm (24 h)
14 114-hh: MI: SS: Mmm (24 h)
-20 or 120 (*) ODBC specification yyyy-mm-dd hh: mm: ss [. fff]
-21 or 121 (*) ODBC specifications (in 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

 

A very powerful date Formatting Function in SQL Server
Select convert (varchar (100), getdate (), 0): 05 16 2006 AM
Select convert (varchar (100), getdate (), 1): 05/16/06
Select convert (varchar (100), getdate (), 2): 06.05.16
Select convert (varchar (100), getdate (), 3): 16/05/06
Select convert (varchar (100), getdate (), 4): 16.05.06
Select convert (varchar (100), getdate (), 5): 16-05-06
Select convert (varchar (100), getdate (), 6): 16 05 06
Select convert (varchar (100), getdate (), 7): 05 16, 06
Select convert (varchar (100), getdate (), 8): 10:57:46
Select convert (varchar (100), getdate (), 9): 05 16 2006 10: 57: 46: 827am
Select convert (varchar (100), getdate (), 10): 05-16-06
Select convert (varchar (100), getdate (), 11): 06/05/16
Select convert (varchar (100), getdate (), 12): 060516
Select convert (varchar (100), getdate (), 13): 16 05 2006 10: 57: 46: 937
Select convert (varchar (100), getdate (), 14): 10: 57: 46: 967
Select convert (varchar (100), getdate (), 20): 10:57:47
Select convert (varchar (100), getdate (), 21): 10:57:47. 157
Select convert (varchar (100), getdate (), 22): 05/16/06 10:57:47 AM
Select convert (varchar (100), getdate (), 23 ):
Select convert (varchar (100), getdate (), 24): 10:57:47
Select convert (varchar (100), getdate (), 25): 10:57:47. 250
Select convert (varchar (100), getdate (), 100): 05 16 2006 AM
Select convert (varchar (100), getdate (), 101): 05/16/2006
Select convert (varchar (100), getdate (), 102): 2006.05.16
Select convert (varchar (100), getdate (), 103): 16/05/2006
Select convert (varchar (100), getdate (), 104): 16.05.2006
Select convert (varchar (100), getdate (), 105): 16-05-2006
Select convert (varchar (100), getdate (), 106): 16 05 2006
Select convert (varchar (100), getdate (), 107): 05 16,200 6
Select convert (varchar (100), getdate (), 108): 10:57:49
Select convert (varchar (100), getdate (), 109): 05 16 2006 10: 57: 49: 437am
Select convert (varchar (100), getdate (), 110): 05-16-2006
Select convert (varchar (100), getdate (), 111): 2006/05/16
Select convert (varchar (100), getdate (), 112): 20060516
Select convert (varchar (100), getdate (), 113): 16 05 2006 10: 57: 49: 513
Select convert (varchar (100), getdate (), 114): 10: 57: 49: 547
Select convert (varchar (100), getdate (), 120): 10:57:49
Select convert (varchar (100), getdate (), 121): 10:57:49. 700
Select convert (varchar (100), getdate (), 126): 2006-05-16t10: 57: 49.827
Select convert (varchar (100), getdate (), 130): 18 ???? ?????? 1427 10: 57: 49: 907am
Select convert (varchar (100), getdate (), 131): 18/04/1427 10: 57: 49: 920am

Frequently used:
Select convert (varchar (100), getdate (), 8): 10:57:46
Select convert (varchar (100), getdate (), 24): 10:57:47
Select convert (varchar (100), getdate (), 108): 10:57:49
Select convert (varchar (100), getdate (), 12): 060516
Select convert (varchar (100), getdate (), 23 ):

 

Usage of convert conversion functions in SQL

How to Use convert:

//////////////////////////////////////// //////////////////////////////////////// ////////

Format:
Convert (data_type, expression [, style])

Note:
This style is generally in the time type (datetime, smalldatetime) and string type (nchar, nvarchar, Char, varchar)
It is used only when they are converted to each other.

Example:
Select convert (varchar (30), getdate (), 101) now
Result:
Now
---------------------------------------
09/15/2001

//////////////////////////////////////// //////////////////////////////////////// /////

The meaning of the style number in the conversion time is as follows:

Bytes -------------------------------------------------------------------------------------------------
Style (2 bits represent the year) | style (4 bits represent the year) | input/output format
Bytes -------------------------------------------------------------------------------------------------
-| 0 or 100 | mon dd yyyy hh: miam (or pm)
Bytes -------------------------------------------------------------------------------------------------
1 | 101 | mm/DD/yy
Bytes -------------------------------------------------------------------------------------------------
2 | 102 | YY-mm-dd
Bytes -------------------------------------------------------------------------------------------------
3 | 103 | dd/mm/yy
Bytes -------------------------------------------------------------------------------------------------
4 | 104 | DD-mm-yy
Bytes -------------------------------------------------------------------------------------------------
5 | 105 | DD-mm-yy
Bytes -------------------------------------------------------------------------------------------------
6 | 106 | dd mon YY
Bytes -------------------------------------------------------------------------------------------------
7 | 107 | mon DD, YY
Bytes -------------------------------------------------------------------------------------------------
Eight | 108 | hh: mm: SS
Bytes -------------------------------------------------------------------------------------------------
-| 9 or 109 | mon dd yyyy hh: MI: SS: mmmmam (or pm)
Bytes -------------------------------------------------------------------------------------------------
10 | 110 | mm-dd-yy
Bytes -------------------------------------------------------------------------------------------------
11 | 111 | YY/MM/dd
Bytes -------------------------------------------------------------------------------------------------
12 | 112 | yymmdd
Bytes -------------------------------------------------------------------------------------------------
-| 13 or 113 | dd mon yyyy hh: MI: SS: Mmm (in 24-hour format)
Bytes -------------------------------------------------------------------------------------------------
14 | 114 | hh: MI: SS: Mmm (in 24-hour format)
Bytes -------------------------------------------------------------------------------------------------
-| 20 or 120 | yyyy-mm-dd hh: MI: SS (in 24-hour format)
Bytes -------------------------------------------------------------------------------------------------
-| 21 or 121 | yyyy-mm-dd hh: MI: SS: Mmm (in 24-hour format)

 

From http://www.cnblogs.com/hantianwei/archive/2009/12/03/1616148.html