Work with data types
Columns and variables used in Transact-SQL each have a data type. The behavior of values in expressions depends on the data type of the column or variable being referenced. For example, as you saw previously, you can use the + operator to concatenate two string values, or to add two numeric values.
The following table shows common data types supported in a SQL Server database.
Exact Numeric
Approximate Numeric
Character
Date/Time
Binary
Other
tinyint
float
char
date
binary
cursor
smallint
real
varchar
time
varbinary
hierarchyid
int
text
datetime
image
sql_variant
bigint
nchar
datetime2
table
bit
nvarchar
smalldatetime
timestamp
decimal/numeric
ntext
datetimeoffset
uniqueidentifier
numeric
xml
money
geography
smallmoney
geometry
Note
For more details on the different data types and their attributes, visit the Transact-SQL reference documentation.
Data type conversion
Compatible data type values can be implicitly converted as required. For example, suppose you can use the + operator to add an integer number to a decimal number, or to concatenate a fixed-length char value and a variable length varchar value. However, in some cases you may need to explicitly convert values from one data type to another - for example, trying to use + to concatenate a varchar value and a decimal value will result in an error, unless you first convert the numeric value to a compatible string data type.
Note
Implicit and explicit conversions apply to certain data types, and some conversions aren't possible. For more information, use the chart in the Transact-SQL reference documentation.
T-SQL includes functions to help you explicitly convert between data types
CAST and TRY_CAST
The CAST function converts a value to a specified data type if the value is compatible with the target data type. An error will be returned if incompatible.
For example, the following query uses CAST to convert the integer values in the ProductID column to varchar values (with a maximum of 4 characters) in order to concatenate them with another character-based value:
SELECT CAST(ProductID AS varchar(4)) + ': ' + Name AS ProductName
FROM Production.Product;
Possible result from this query might look something like this:
ProductName
680: HL Road Frame - Black, 58
706: HL Road Frame - Red, 58
707: Sport-100 Helmet, Red
708: Sport-100 Helmet, Black
...
However, let's suppose the Size column in the Production.Product table is a nvarchar (variable length, Unicode text data) column that contains some numeric sizes (like 58) and some text-based sizes (like "S", "M", or "L"). The following query tries to convert values from this column to an integer data type:
SELECT CAST(Size AS integer) As NumericSize
FROM Production.Product;
This query results in the following error message:
Error: Conversion failed when converting the nvarchar value 'M' to data type int.
Given that at least some of the values in the column are numeric, you might want to convert those values and ignore the others. You can use the TRY_CAST function to convert data types.
SELECT TRY_CAST(Size AS integer) As NumericSize
FROM Production.Product;
The results this time look might like this:
NumericSize
58
58
NULL
NULL
...
The values that can be converted to a numeric data type are returned as decimal values, and the incompatible values are returned as NULL, which is used to indicate that a value is unknown.
Note
We'll explore considerations for handling NULL values later in this unit.
CONVERT and TRY_CONVERT
CAST is the ANSI standard SQL function for converting between data types, and is used in many database systems. In Transact-SQL, you can also use the CONVERT function, as shown here:
SELECT CONVERT(varchar(4), ProductID) + ': ' + Name AS ProductName
FROM Production.Product;
Once again, this query returns the value converted to the specified data type, like this:
ProductName
680: HL Road Frame - Black, 58
706: HL Road Frame - Red, 58
707: Sport-100 Helmet, Red
708: Sport-100 Helmet, Black
...
Like CAST, CONVERT has a TRY_CONVERT variant that returns NULL for incompatible values.
Another benefit of using CONVERT over CAST is that CONVERT also includes a parameter that enables you specify a format style when converting numeric and date values to strings. For example, consider the following query:
SELECT SellStartDate,
CONVERT(varchar(20), SellStartDate) AS StartDate,
CONVERT(varchar(10), SellStartDate, 101) AS FormattedStartDate
FROM SalesLT.Product;
The results from this query might look something like this:
SellStartDate
StartDate
FormattedStartDate
2002-06-01T00:00:00.0000000
Jun 1 2002 12:00AM
6/1/2002
2002-06-01T00:00:00.0000000
Jun 1 2002 12:00AM
6/1/2002
2005-07-01T00:00:00.0000000
Jul 1 2005 12:00AM
7/1/2005
2005-07-01T00:00:00.0000000
Jul 1 2005 12:00AM
7/1/2005
...
...
...
Note
To find out more about style formatting codes you can use with CONVERT, see the Transact-SQL reference documentation.
PARSE and TRY_PARSE
The PARSE function is designed to convert formatted strings that represent numeric or date/time values. For example, consider the following query (which uses literal values rather than values from columns in a table):
SELECT PARSE('01/01/2021' AS date) AS DateValue,
PARSE('$199.99' AS money) AS MoneyValue;
The results of this query look like this:
DateValue
MoneyValue
2021-01-01T00:00:00.0000000
199.99
Similarly to CAST and CONVERT, PARSE has a TRY_PARSE variant that returns incompatible values as NULL.
Note
When working with decimal or numeric data types, you may need to round to a whole number or set the decimal point, which can be achieved through precision and scale. To better understand this concept of precision and scale, see the Transact-SQL reference documentation.
STR
The STR function converts a numeric value to a varchar.
For example:
SELECT ProductID, '$' + STR(ListPrice) AS Price
FROM Production.Product;
The results would look something like this:
ProductID
Price
680
$1432.00
706
$1432.00
707
$35.00
...
...