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

Leave a Comment