SQL Server Type Conversion

Type Conversion

CONVERT function is used to convert the data type from one type to another.

SQL Server cannot convert the data type implicitly, need to change the data type explicitly.

CONVERT Function

CONVERT function is used to change the data type from one to another type.

Only we can change the data type to system defined type and cannot use user defined type in the type conversion.

CONVERT Function Syntax

CONVERT(datatype[(length)], expression[, format])

here expression can be column name, value or expression. length is the optional parameter which can be specified with char, varchar data types.

format parameter is optional which can be used to convert date type into string.

CONVERT Function Example

SELECT CONVERT(int, '20')

20

here converts string '20' into integer value.

SELECT Id, Name, City, Country, 'Salary'=CONVERT(VARCHAR(10), Salary) + ' USD'
FROM Employee

here converts Salary column value into string and concatenates ' USD' in trailing.

Date Type to Char Conversion

format parameter can be used to specify the date format to be used for string formatting.

SELECT 'date'=CONVERT(CHAR(20), GETDATE(), 0)

Nov 26 2017  3:50AM 

SELECT 'date'=CONVERT(CHAR(20), GETDATE(), 1)

11/26/17            

SELECT 'date'=CONVERT(CHAR(20), GETDATE(), 101)

11/26/2017                      

CONVERT Function Date Format

• 0 or 100 : mon dd yyyy hh:mm [AM or PM]
• 1 : mm/dd/yy
• 2 : yy.mm.dd
• 3 : dd/mm/yy
• 4 : dd.mm.yy
• 5 : dd-mm-yy
• 101 : mm/dd/yyyy
• 102 : yyyy.mm.dd
• 103 : dd/mm/yyyy
• 104 : dd.mm.yyyy
• 105 : dd-mm-yyyy