What is SQL CAST Function?

The CAST() function receives an input value and converts it from one datatype to another datatype specified. In MS SQL Server, CAST() is similar to the CONVERT() function.

Syntax

Here is the generic syntax for CAST function:

CAST(expression AS datatype(length))

Classified datatype casting

1. Casting numbers

A numeric value can be cast to a numeric datatype or to a character datatype.

When casting a long number value to a shorter one, the value is truncated, not rounded. For example, when casting 32.99 to INT, it returns 32.

2. Casting Character Strings

A character value can be cast to another character datatype, and in some special cases, it can be cast to a datetime value.

3. Casting NULL

NULL can be cast to any datatype and returns NULL.

4. Casting empty strings

The empty string (‘’) casts as follows:

  • All character datatypes return NULL.
  • All numeric datatypes return 0 (zero), with the appropriate number of trailing fractional zeros. * The DOUBLE datatype returns zero with no trailing fractional zeros.
  • The DATE datatype returns 12/31/1840.
  • The TIME datatype returns 00:00:00.
  • The TIMESTAMP, DATETIME, and SMALLDATETIME datatypes return NULL.
  • The BIT datatype returns 0.
  • All binary datatypes return NULL.

5. Casting datetime values

You can cast a datetime value to a datetime datatype, to a numeric datatype, or a character datatype.

6. Casting a Bit Value

You can cast an expression value AS BIT to return a 0 or 1.

  • If the expression is 1 or any other non-zero numeric value, it returns 1.

  • If the expression is “TRUE”, “True”, or “true”, it returns 1.

  • If the expression is any other non-numeric value, it returns 0. If the expression is 0, it returns 0.

Example

To convert a value to a date time value:

SELECT CAST('2019-09-05' AS datetime);

Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL and many other databases simultaneously using an intuitive and powerful graphical interface.

Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.

Need a quick edit on the go? Download for iOS

TablePlus in Dark mode