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 |