In SQL Server, the CONVERT function is used to change the data type of a value. The syntax for the CONVERT function is as follows:
CONVERT(data_type, expression [, style])
- data_type: The target data type you want to convert the value into. It must be a valid SQL Server data type, like VARCHAR, INT, DATETIME, etc.
- expression: The value or column that you want to convert.
- style (optional): An integer that specifies the format of the conversion. This parameter is particularly relevant when converting date/time types or decimal/floating point numbers.
Example Usage
1. Converting a String to an Integer:
SELECT CONVERT(INT, '123') AS ConvertedValue;
This will convert the string '123' into the integer 123.
2. Converting an Integer to a String:
SELECT CONVERT(VARCHAR(10), 123) AS ConvertedValue;
This will convert the integer 123 into a VARCHAR string with the value '123'.
3. Converting a String to a Date:
SELECT CONVERT(DATETIME, '2023-10-17', 120) AS ConvertedDate;
This will convert the string '2023-10-17' into a DATETIME value.
4. Using the style parameter:
When converting dates, you may specify a style to control the output format. For example:
SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS FormattedDate;
The style 101 will format the date to mm/dd/yyyy.
5. Converting a Decimal Number to Integer:
SELECT CONVERT(INT, 123.456) AS ConvertedValue;
This will convert the decimal number 123.456 to the integer 123 (the fractional part is truncated).
Notes
- When converting types, if the conversion cannot be performed (e.g., converting a non-numeric string to a numeric type), SQL Server will return an error.
- Make sure to choose the appropriate data type and style to avoid data loss and ensure the correctness of the operation.