Conversion functions

Overview

These functions support data type casting and conversion. Conversion functions convert an expression of one data type to another data type. The conversion functions covered in this section are:

• ​CAST​
• ​CONVERT​

Both 'CAST' and 'CONVERT' functions perform the same basic operation of data type conversion in SQL. The key difference lies in their flexibility. 'CONVERT' has an additional style parameter, which makes it more flexible in formatting output data.

CAST

This function is used to translate an expression of one data type to another.

Syntax

CAST ( expression AS data_type [ ( length ) ] )

Return Type

Returns expression, translated to data_type

Arguments

Argument	Description	Example
expression	An expression to translate.	1.5
data_type	The data type to translate the expression into.	int
length	An optional integer that specifies the length of the target data type, for data types that allow a user specified length.

Example

The below example translates the expression 1.5 as an integer value.

SELECT CAST(1.5 AS int)

CONVERT

This function is used to convert an expression of one data type to another.

Syntax

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Return Type

Returns expression, converted to data_type

Arguments

Argument	Description	Example
data_type	The data type to convert the expression into.	datetime
length	An optional integer that specifies the length of the target data type, for data types that allow a user specified length.
expression	An expression to convert.	2020-01-01
style	An optional integer expression that specifies how the CONVERT function will translate expression. For a style value of NULL, NULL is returned.	103

Converting datetime to character

Without century	With century	Input/Output	Standard
0	100	mon dd yyyy hh:miAM/PM	Default
1	101	mm/dd/yyyy	US
2	102	yyyy.mm.dd	ANSI
3	103	dd/mm/yyyy	British/French
4	104	dd.mm.yyyy	German
5	105	dd-mm-yyyy	Italian
6	106	dd mon yyyy	-
7	107	Mon dd, yyyy	-
8	108	hh:mm:ss	-
9	109	mon dd yyyy hh:mi:ss:mmmAM (or PM)	Default + millisec
10	110	mm-dd-yyyy	USA
11	111	yyyy/mm/dd	Japan
12	112	yyyymmdd	ISO
13	113	dd mon yyyy hh:mi:ss:mmm	Europe (24 hour clock)
14	114	hh:mi:ss:mmm	24 hour clock
20	120	yyyy-mm-dd hh:mi:ss	ODBC canonical (24 hour clock)
21	121	yyyy-mm-dd hh:mi:ss.mmm	ODBC canonical (24 hour clock)
-	126	yyyy-mm-ddThh:mi:ss.mmm	ISO8601
-	127	yyyy-mm-ddThh:mi:ss.mmmZ	ISO8061 (with time zone Z)
-	130	dd mon yyyy hh:mi:ss:mmmAM	Hijiri
-	131	dd/mm/yy hh:mi:ss:mmmAM	Hijiri

Converting float to real

Value	Explanation
0	Maximum 6 digits (default)
1	8 digits
2	16 digits

Converting money to character

Value	Explanation
0	No comma delimiters, 2 digits to the right of decimal
1	Comma delimiters, 2 digits to the right of decimal
2	No comma delimiters, 4 digits to the right of decimal

Example 1

The below example converts '2020-01-01' to datetime.

SELECT CONVERT(datetime, '2020-01-01')

Example 2

The below example converts a text valued column (Date of Purchase) into a date valued column (Event Date).

103 refers to the style code.

Syntax

SELECT [Text Date Column],
CONVERT(DATE, [Your Text Date Column], 103) AS `Converted Date Column`
FROM [Domain].[Table]
WHERE [Deleted] IS NULL

Example

SELECT [Date of Purchase],
CONVERT(DATE, [Date of Purchase], 103) AS `EventDate`
FROM [Your Domain].[Your Table]
WHERE [Deleted] IS NULL