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