Tutorials
Search…
Lesson 26. Casting Data Types
When you are throwing around string values, SQL Server does its best to try to determine what the actual data type is. The software is really good at identifying dates, integers, and floats when they are stored as NVARCHAR. When you do basic arithmetic, you can implicitly cast data types.
However, sometimes its best is not good enough and you have to step in and specify what data type a value should be, or perhaps you have to change the data type of an existing data type. Sometimes, if you are REALLY lucky you will be dealing with a data type that blows up a deterministic process because it makes the process non-deterministic, in which case you have to cast the data to a type that is more friendly to deterministic processes.
This is where the functions CAST and CONVERT come in handy. They both do the same thing, but I VASTLY prefer the CAST syntax. However, if you want to format data, you will have to use the CONVERT syntax.

Examples

Implicit Cast
In [ ]:
USE demo
SELECT 1/1
SELECT 1/1.0
Explicit Cast With CAST
In [ ]:
DECLARE @Value INT = 25
SELECT @Value AS IntValue
SELECT CAST(@Value AS NUMERIC(18,4)) AS DecimalValue
Formatting A Date With CONVERT
In [ ]:
USE demo
SELECT CURRENT_TIMESTAMP AS TheDate
SELECT CONVERT(NVARCHAR(30), CURRENT_TIMESTAMP, 101) AS TheDate
Copy link