The SQL CONVERT() is a SQL Server function which is used to convert a data type to another data type in general. We can also use this function to convert a date/time expression to different formats, or convert a formatted date/time string to date expression.
SQL CONVERT() Function Syntax
CONVERT(data_type(length), data_value, style)
data_type(length): the target data type, which length is optional.
data_value: the data to be converted.
style: the format of date/time.
The style could be one of the following:
Style | Format |
---|---|
100 or 0 | mon dd yyyy hh:miAM (or PM) – Nov 13 2011 3:17PM |
101 | mm/dd/yyyy – 11/13/2011 |
102 | yyyy.mm.dd – 2011.11.13 |
103 | dd/mm/yyyy – 13/11/2011 |
104 | dd.mm.yyyy – 13.11.2011 |
105 | dd-mm-yyyy – 13-11-2011 |
106 | dd mon yyyy – 13 Nov 2011 |
107 | Mon dd, yyyy – Nov 13, 2011 |
108 | hh:mm:ss – 15:25:08 |
109 or 9 | mon dd yyyy hh:mi:ss:mmmAM (or PM) – Nov 13 2011 3:25:52:687PM |
110 | mm-dd-yyyy – 11-13-2011 |
111 | yyyy/mm/dd – 2011/11/13 |
112 | yyyymmdd – 20111113 |
113 | dd mon yyyy hh:mm:ss:mmm(24h) – 13 Nov 2011 15:28:39:750 |
114 | hh:mi:ss:mmm(24h) – 15:29:14:577 |
120 or 20 | yyyy-mm-dd hh:mi:ss(24h) – 2011-11-13 15:29:53 |
121 or 21 | yyyy-mm-dd hh:mi:ss.mmm(24h) – 2011-11-13 15:30:31.500 |
126 | yyyy-mm-ddThh:mm:ss.mmm(no spaces) – 2011-11-13T15:31:07.123 |
127 | yyyy-mm-ddThh:mi:ss.mmmZ – 2011-11-13T15:39:35.220 |
130 | dd mon yyyy hh:mi:ss:mmmAM – 17 Nov 2011 1432 3:33:38:813PM |
131 | dd/mm/yy hh:mi:ss:mmmAM – 17/12/1432 3:35:36:720PM |
SQL CONVERT() Function Example
SELECT CONVERT(VARCHAR(19), GETDATE(), 100) SELECT CONVERT(VARCHAR(10), GETDATE(), 104) SELECT CONVERT(VARCHAR(19), GETDATE(), 120)
The result will look like:
Nov 13 2011 4:10PM 13.11.2011 2011-11-13 16:11:19
We can also convert a string to date/time type:
SELECT CONVERT(DATETIME, 'Nov 23 2012 11:53:44:456AM', 109)
The result will look like:
2012-11-23 11:53:44.457 |
Convert string to numbers:
SELECT CONVERT(INT, '2022')
The result will look like:
2022 |