MS SQL Server provides some functions in order to make database administrators life easier. A typical database store different type 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 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
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
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);