How to Use the CONVERT() Function in SQL Server: A Complete Guide
☰Fullscreen
The CONVERT() function converts a value (of any type) into a specified datatype.
Syntax:
<span class="pln"> CONVERT</span><span class="pun">(</span><span class="pln">data_type</span><span class="pun">(</span><span class="pln">length</span><span class="pun">),</span><span class="pln"> expression</span><span class="pun">,</span><span class="pln"> style</span><span class="pun">)</span><span class="pln"> </span>
Parameter Values
| Value | Description |
|---|---|
| data_type | Required. The datatype to convert expression to. Can be one of the following: bigint, int, smallint, tinyint, bit, decimal, numeric, money, smallmoney, float, real, datetime, smalldatetime, char, varchar, text, nchar, nvarchar, ntext, binary, varbinary, or image |
| (length) | Optional. The length of the resulting data type (for char, varchar, nchar, nvarchar, binary and varbinary) |
| expression | Required. The value to convert to another data type |
| style | Optional. The format used to convert between data types, such as a date or string format. Can be one of the following values: |
Converting datetime to character:
| Without century | With century | Input/Output | Standard |
|---|---|---|---|
| 0 | 100 | mon dd yyyy hh:miAM/PM | Default |
| 1 | 101 | mm/dd/yyyy | US |
| 2 | 102 | yyyy.mm.dd | ANSI |
| 3 | 103 | dd/mm/yyyy | British/French |
| 4 | 104 | dd.mm.yyyy | German |
| 5 | 105 | dd-mm-yyyy | Italian |
| 6 | 106 | dd mon yyyy | - |
| 7 | 107 | Mon dd, yyyy | - |
| 8 | 108 | hh:mm:ss | - |
| 9 | 109 | mon dd yyyy hh:mi:ss:mmmAM (or PM) | Default + millisec |
| 10 | 110 | mm-dd-yyyy | USA |
| 11 | 111 | yyyy/mm/dd | Japan |
| 12 | 112 | yyyymmdd | ISO |
| 13 | 113 | dd mon yyyy hh:mi:ss:mmm | Europe (24 hour clock)> |
| 14 | 114 | hh:mi:ss:mmm | 24 hour clock |
| 20 | 120 | yyyy-mm-dd hh:mi:ss | ODBC canonical (24 hour clock) |
| 21 | 121 | yyyy-mm-dd hh:mi:ss.mmm | ODBC canonical (24 hour clock) |
| 126 | yyyy-mm-ddThh:mi:ss.mmm | ISO8601 | |
| 127 | yyyy-mm-ddThh:mi:ss.mmmZ | ISO8601 (with time zone Z) | |
| 130 | dd mon yyyy hh:mi:ss:mmmAM | Hijiri | |
| 131 | dd/mm/yy hh:mi:ss:mmmAM | Hijiri |
Converting float to real:
| Value | Explanation |
|---|---|
| 0 | Maximum 6 digits (default) |
| 1 | 8 digits |
| 2 | 16 digits |
Converting money to character:
| Value | Explanation |
|---|---|
| 0 | No comma delimiters, 2 digits to the right of decimal |
| 1 | Comma delimiters, 2 digits to the right of decimal |
| 2 | No comma delimiters, 4 digits to the right of decimal |
Code:
<span class="pln"> </span><span class="pun">--</span><span class="pln"> </span><span class="typ">Convert</span><span class="pln"> an expression </span><span class="kwd">from</span><span class="pln"> one data type to another </span><span class="pun">(</span><span class="pln">varchar</span><span class="pun">):</span><span class="pln"> SELECT CONVERT</span><span class="pun">(</span><span class="pln">varchar</span><span class="pun">,</span><span class="pln"> </span><span class="lit">25.65</span><span class="pun">);</span><span class="pln"> </span><span class="pun">--</span><span class="pln"> </span><span class="typ">Convert</span><span class="pln"> an expression </span><span class="kwd">from</span><span class="pln"> one data type to another </span><span class="pun">(</span><span class="pln">datetime</span><span class="pun">):</span><span class="pln"> SELECT CONVERT</span><span class="pun">(</span><span class="pln">datetime</span><span class="pun">,</span><span class="pln"> </span><span class="str">'2017-08-25'</span><span class="pun">);</span><span class="pln"> </span><span class="pun">--</span><span class="typ">Convert</span><span class="pln"> an expression </span><span class="kwd">from</span><span class="pln"> one data type to another </span><span class="pun">(</span><span class="pln">varchar</span><span class="pun">):</span><span class="pln"> SELECT CONVERT</span><span class="pun">(</span><span class="pln">varchar</span><span class="pun">,</span><span class="pln"> </span><span class="str">'2017-08-25'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">101</span><span class="pun">);</span><span class="pln"> </span>
Output:
The above code will produce the following result-
<span class="pln"> </span><span class="lit">25.65</span><span class="pln"> </span><span class="lit">2017</span><span class="pun">-</span><span class="lit">08</span><span class="pun">-</span><span class="lit">25</span><span class="pln"> </span><span class="lit">00</span><span class="pun">:</span><span class="lit">00</span><span class="pun">:</span><span class="lit">00.000</span><span class="pln"> </span><span class="lit">2017</span><span class="pun">-</span><span class="lit">08</span><span class="pun">-</span><span class="lit">25</span><span class="pln"> </span>
Consider the Employees Table below
The following queries convert, DateOfBirth's DateTime datatype to NVARCHAR.
Code:
<span class="pln"> </span><span class="typ">Select</span><span class="pln"> </span><span class="typ">Id</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Name</span><span class="pun">,</span><span class="pln"> DOB</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Convert</span><span class="pun">(</span><span class="pln">nvarchar</span><span class="pun">,</span><span class="pln"> DOB</span><span class="pun">,</span><span class="pln"> </span><span class="lit">103</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> </span><span class="typ">ConvertedDOB</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> </span><span class="typ">Employee</span><span class="pln"> </span>
Prerequisite Code:
<span class="pln"> </span><span class="typ">Drop</span><span class="pln"> table </span><span class="typ">Employee</span><span class="pln"> </span><span class="typ">Create</span><span class="pln"> </span><span class="typ">Table</span><span class="pln"> </span><span class="typ">Employee</span><span class="pun">(</span><span class="pln"> </span><span class="typ">Id</span><span class="pln"> </span><span class="kwd">int</span><span class="pln"> NOT NULL primary key</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Name</span><span class="pln"> nvarchar</span><span class="pun">(</span><span class="lit">50</span><span class="pun">),</span><span class="pln"> DOB datetime </span><span class="pun">)</span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">Employee</span><span class="pln"> values </span><span class="pun">(</span><span class="str">'1'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Rambo'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'1986-11-14 08:26:00.000'</span><span class="pun">)</span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">Employee</span><span class="pln"> values </span><span class="pun">(</span><span class="str">'2'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Azam'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'1984-10-10 03:32:00.000'</span><span class="pun">)</span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">Employee</span><span class="pln"> values </span><span class="pun">(</span><span class="str">'3'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Inza'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'1996-07-26 08:26:00.000'</span><span class="pun">)</span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">Employee</span><span class="pln"> values </span><span class="pun">(</span><span class="str">'4'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Jaman'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'1990-11-02 03:32:00.000'</span><span class="pun">)</span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">Employee</span><span class="pln"> values </span><span class="pun">(</span><span class="str">'5'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Asad'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'1991-01-03 03:32:00.000'</span><span class="pun">)</span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">Employee</span><span class="pln"> values </span><span class="pun">(</span><span class="str">'6'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'kamran'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'1990-11-02 03:32:00.000'</span><span class="pun">)</span><span class="pln"> </span><span class="typ">Select</span><span class="pln"> </span><span class="pun">*</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> </span><span class="typ">Employee</span><span class="pln"> </span>
No Questions Data Available.
No Program Data.