Cast Conversion Functions

Casting and conversion functions are available for all built-in system data types.

Casting functions

FunctionReturn typeDescriptionExampleResult
CAST(col_expr AS data type)same as requested data typeconverts the value of the col_expr into the specified data typecast(123 as varchar(10))'123'
CONVERT(col_expr , data type)same as requested data typeconverts the value of the col_expr into the specified data typeconvert(123, decimal(10,3))123.000

Examples

select cast(true as smallint);
select cast(42 as int);
select cast(123.45 as real);
select cast('123.45' as double precision);
select cast(23.45 as decimal(5,2));    -- precision of 5 digits of which 2 decimal digits

select cast('2020-07-29' as date);
select cast('17:44:59' as time);
select cast('17:44:59.123456' as time);
select cast('2020-07-29 17:44:59' as timestamp);
select cast('2020-07-29T17:44:59' as timestamp);
select cast('2020-07-29 17:44:59.123456' as timestamp);
select cast('17:44:59.321+01:30' as timetz);
select cast('2020-07-29 17:44:59.321+01:30' as timestamptz);

select cast('67' as interval month);
select cast('120' as interval day);
select cast('86400.123' as interval second);
select cast('18:40:05' as interval hour to second);
select cast('2 18:40:05.123' as interval day to second(3));
select cast('2-5' as interval year to month);

select cast('a4cd' as blob);    -- use 2 hex digits per byte, so 'a4cd' is 2 bytes long.
select cast('abcde' as clob);
select cast('192.168.1.5/24' as inet);
select cast(r'{"a":[1,2,4]}' as json);
select cast('https://www.monetdb.org/Home' as url);
select cast('e31960fb-dc8b-452d-ab30-b342723e756a' as uuid);

-- or using convert instead of cast:
select convert('a4cd' , blob);
select convert('abcde' , clob);
select convert('192.168.1.5/24' , inet);
select convert(r'{"a":[1,2,4]}' , json);
select convert('https://www.monetdb.org/Home' , url);
select convert('e31960fb-dc8b-452d-ab30-b342723e756a' , uuid);

Casting operators for character string literals

OperatorDescriptionExample
E'string with backslash escapes'convert backslash escapes (such as: \f \t \n \b \u etc) in string as specific (control) characterse'A\fB\tC\n\Z'
R'any string'NO conversion of backslash escapes is done. Treat string characters as provided, so rawr'A\fB\tC\n\Z'
x'hexadecimals'convert string value (pairs of hexadecimals) to binary string (blob)x'abcd'
blob 'hexadecimals'convert string value (pairs of hexadecimals) to blob typeblob 'abcd'
dateconvert YYYY-MM-DD formatted string value to a date typeselect date '2020-09-28'
timeconvert HH:MI:SS formatted string value to a time typeselect time '18:40:05'
time(6)convert HH:MI:SS.ssssss formatted string value to a time(6) typeselect time(6) '18:40:05.123456'
time(3) with time zoneconvert HH:MI:SS.sss+MI:SS formatted string value to a time(3) with time zone typeselect time(3) with time zone '18:40:05.123+02:00'
timestampconvert YYYY-MM-DD HH:MI:SS formatted string value to a timestamp typeselect timestamp '2020-09-28 18:40:05'
timestamp(6)convert YYYY-MM-DD HH:MI:SS.ssssss formatted string value to a timestamp(6) typeselect timestamp(6) '2020-09-28 18:40:05.123456'
timestamp(3) with time zoneconvert YYYY-MM-DD HH:MI:SS.sss+MI:SS formatted string value to a timestamp(3) with time zone typeselect timestamp(3) with time zone '2020-09-28 18:40:05.123+02:00'
intervalconvert number value string to second interval typeselect interval '67205' second
intervalconvert number value string to day interval typeselect interval '30' day
intervalconvert number value string to month interval typeselect interval '6' month
intervalconvert HH:MI:SS formatted string value to second interval typeselect interval '18:40:05' hour to second
intervalconvert DD HH:MI:SS formatted string value to second interval typeselect interval '2 18:40:05.123' day to second(3)
intervalconvert YYYY-MM formatted string value to month interval typeselect interval '2-5' year to month
inet 'valid inet address string'convert string value to inet typeinet '192.168.1.5/24'
json 'valid json string'convert string value to json typejson '{"a":[1,2,4]}'
url 'valid url string'convert string value to url typeurl 'https://www.monetdb.org/Home'
uuid 'valid uuid string'convert string value to uuid typeuuid 'e31960fb-dc8b-452d-ab30-b342723e756a'