SQL Server CONVERT () function usage summary

CONVERTUsage:

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

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)
Bytes -------------------------------------------------------------------------------------------------

AvailableStyleValue:

Style ID	Style format
100 or 0	Mon dd yyyy hh: miAM (or PM)
101	Mm/dd/yy
102	Yy. mm. dd
103	Dd/mm/yy
104	Dd. mm. yy
105	Dd-mm-yy
106	Dd mon yy
107	Mon dd, yy
108	Hh: mm: ss
109 or 9	Mon dd yyyy hh: mi: ss: mmmAM (or PM)
110	Mm-dd-yy
111	Yy/mm/dd
112	Yymmdd
113 or 13	Dd mon yyyy hh: mm: ss: mmm (24 h)
114	Hh: mi: ss: mmm (24 h)
120 or 20	Yyyy-mm-dd hh: mi: ss (24 h)
121 or 21	Yyyy-mm-dd hh: mi: ss. mmm (24 h)
126	Yyyy-mm-ddThh: mm: ss. mmm (no space)
130	Dd mon yyyy hh: mi: ss: mmmAM
131	Dd/mm/yy hh: mi: ss: mmmAM

 

Convert function application of SqlServer

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