The CONVERT() function converts one data type to another. It has two parameters, the first parameter of the function is the data type to which you wish the existing column or expression to be converted.
SQL Server will attempt to convert like data types to the proper type automatically when it can, otherwise it returns an error message that suggest the use of CONVERT().
Example:
Suppose we have a table ‘tbl_employee’
Table name: tbl_Employee
| Employee Id | EmpName | Salary | Department |
| 1 | Ankur | 1200 | Sales |
| 2 | David | 3400 | Sales |
| 3 | John | 1500 | HR |
| 4 | James | 1200 | Production |
| 5 | Mohan | 4500 | Production |
| 6 | Ram | 2300 | HR |
See the following query ( we create a Employee code with the help of Employee name and Employee id).
SELECT EmpName,
CONVERT(CHAR(1),Employee Id) + ‘-‘ + LEFT(EmpName,3) AS “Employee Code”
FROM tbl_Employee
Output Will be:
| EmpName | Employee Code |
| Ankur | Ank-1 |
| David | Dav-2 |
| John | Joh-3 |
| James | Jam-4 |
| Mohan | Moh-5 |
| Ram | Ram-6 |
One more important use of this function is to specify the format of a date field when converting it to CHAR or VARCHAR from DATETIME .
SQL Server string to datetime conversion /convert char to date / convert varchar to date
Example
SELECT convert(datetime, ‘Oct 23 2012 11:01AM’)
mm/dd/yyyy format
SELECT convert(datetime, ’10/23/2016′)
yyyy.mm.dd format
SELECT convert(datetime, ‘2016.10.23’)
The CONVERT() function can be used to display datetime data in different formats by using an additional parameter.
Then you can use the CONVERT() function as:
CONVERT(data-type, _expression, _style)
where
data-type is Specifies the target data type (with an optional length).
_expression is Specifies the value to be converted.
_style is Specifies the output format for the date/time.