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 Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>