How to convert a value of one type to another type in SQL server
Convert means to change the form or value of something. The CONVERT() function in SQL server is used to convert a value of one type to another type.
Syntax :
SELECT CONVERT ( target_type ( length ), expression )
Parameters used :
- target_type –
It is the target data type to which the to expression will be converted, e.g: INT, BIT, SQL_VARIANT, etc. - length –
It provides the length of the target_type. Length is not mandatory. Default length is set to 30. - expression –
expression is anything that will be converted.
Example-1 :
To convert a decimal to an integer :
In below example, the CONVERT() function is used to convert the decimal number 7.85 to an integer.
SELECT CONVERT(INT, 7.85) AS Result;
Output :
Result |
---|
7 |
Example-2 :
To convert a decimal to another decimal :
In below example, the CONVERT() function is used to convert the decimal number 8.99 to another decimal number with zero scales.
SELECT CAST(8.99 AS DEC(2, 0)) AS Result;
Output :
Result |
---|
9 |
Example-3 :
To convert a string to a datetime value :
In below example, the CONVERT() function is used to convert the string ‘2020-05-14’ to a datetime value.
SELECT CONVERT(DATETIME, '2020-05-14') AS Result;
Output :
Result |
---|
2019-03-14 00:00:00.000 |
Example-4 :
To convert a datetime value to a string :
In below example, the CONVERT() function is used to convert the current date and time to a string with a definite style.
SELECT CONVERT(VARCHAR, GETDATE(), 13) AS Result;
Output :
Result |
---|
05 Sep 2020 16:59:01:380 |