MS SQL Server provides some functions in order to make database administrators’ life easier. A typical database store different types of values. In some cases, we may need to cast and convert these values into different data types. MS SQL provides CAST()
function can be used to change data types.
CAST() Function Syntax
CAST() function has a simple syntax where we will provide the value and the type we want to cast to.
CAST(expression AS datatype(length))
expression
is the value, variable, or expression we want to convert.datatype
is the data type we want to convert to.length
is optional where varchar or similar data types require length for the variable storage.
Cast String To DateTime
Date and times can be expressed in string format but MS SQL also provides DateTime date type which provides special functions about date and time. In this example, we will provide some data in string format and convert it into MS SQL DateTime format.
SELECT CAST('2017-08-25 12:05:34.789' AS datetime);
We can see from the screenshot that the date which consists of year, month, day and time which also consist of the hour, minute, second and millisecond converted into datetime
type.
Cast String To Date
In the previous example, we have converted a string into DateTime format which is a compound type for date and time. We can also convert or cast just the string into a date.
SELECT CAST('2017-08-25' AS date);
Cast String To Time
We can also just cast or convert a string into a time which contains hour, minute, second, a millisecond.
SELECT CAST('12:13:14.567' AS time);
Cast Number To Character
We can cast a number into a character or character array/string. We will use char
data type in this example but other char types like nchar
, varchar
can be also used.
SELECT CAST( 67 AS char);
Cast String To Number
We can also provide numbers in string format and convert them into number
data type easily.
SELECT CAST( '123' AS number);
Cast String To Floating Point
number
data type only stores integers and floating points can not be stored. We can cast a string into a floating-point number with the float
data type.
SELECT CAST( '123.456' AS float);