Black friday

Save big!

All courses under $5 - for a limited time!

Code has been added to clipboard!

Using the SQL CONVERT Function

Reading time 2 min
Published Aug 10, 2017
Updated Oct 10, 2019

SQL CONVERT Function: Main Tips

  • The CONVERT function is used to convert the data format.
  • The CONVERT function can be useful for displaying time/date in different formats.

Syntax of Convert Function

Converts an expression of one data type to another

Example
CONVERT(data_type(length),expression,style)

SQL CONVERT: Syntax Values

Value Description
data_type(length) Defines the data type of the target (length is optional)
expression Defines what the value has to be converted
style Defines the format of the output for the time/date (see table below)

SQL CONVERT: Style Values

Without century With century Input/Output Standard
- 0 or 100 mon dd yyyy hh:miAM (or PM) Default
1 101 1 = mm/dd/yy
101 = mm/dd/yyyy
USA
2 102 2 = yy.mm.dd
102 = yyyy.mm.dd
ANSI
3 103 3 = dd/mm/yy
103 = dd/mm/yyyy
British/French
4 104 4 = dd.mm.yy
104 = dd.mm.yyyy
German
5 105 5 = dd-mm-yy
105 = dd-mm-yyyy
Italian
6 106 6 = dd mon yy
106 = dd mon yyyy
-
7 107 7 = Mon dd, yy
107 = Mon dd, yyyy
-
8 108 hh:mm:ss -
- 9 or 109 mon dd yyyy hh:mi:ss:mmmAM (or PM) Default + millisec
10 110 10 = mm-dd-yy
110 = mm-dd-yyyy
USA
11 111 11 = yy/mm/dd
111 = yyyy/mm/dd
Japan
12 112 12 = yymmdd
112 = yyyymmdd
ISO
- 13 or 113 dd mon yyyy hh:mi:ss:mmm (24h) Europe default + millisec
14 114 hh:mi:ss:mmm (24h) -
- 20 or 120 yyyy-mm-dd hh:mi:ss (24h) ODBC canonical
- 21 or 121 yyyy-mm-dd hh:mi:ss.mmm (24h) ODBC canonical (with milliseconds) default for time, date, datetime2, and datetimeoffset
- 126 yyyy-mm-ddThh:mi:ss.mmm (no spaces) ISO8601
- 127 yyyy-mm-ddThh:mi:ss.mmmZ (no spaces) ISO8601 with time zone Z
- 130 dd mon yyyy hh:mi:ss:mmmAM Hijiri
- 131 dd/mm/yy hh:mi:ss:mmmAM Hijiri

SQL CONVERT: Example

This example uses the CONVERT() function for displaying information in different formats. In this case GETDATE() function is used.

Example
CONVERT(VARCHAR(19),GETDATE())
CONVERT(VARCHAR(10),GETDATE(),110)
CONVERT(VARCHAR(10),GETDATE(),10)
CONVERT(VARCHAR(11),GETDATE(),106)
CONVERT(VARCHAR(11),GETDATE(),6)
CONVERT(VARCHAR(24),GETDATE(),113)

This is the result:
Dec 05 2016 10:29 PM
12-05-2016
12-05-16
05 Dec 2016
05 Dec 16
05 Dec 2016 10:29:69:359