SQL CONVERT

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