Casting and conversion functions are available for all built-in system data types.
Casting functions
| Function | Return type | Description | Example | Result |
|---|---|---|---|---|
| CAST(col_expr AS data type) | same as requested data type | converts the value of the col_expr into the specified data type | cast(123 as varchar(10)) | '123' |
| CONVERT(col_expr , data type) | same as requested data type | converts the value of the col_expr into the specified data type | convert(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);
| Operator | Description | Example |
|---|---|---|
| E'string with backslash escapes' | convert backslash escapes (such as: \f \t \n \b \u etc) in string as specific (control) characters | e'A\fB\tC\n\Z' |
| R'any string' | NO conversion of backslash escapes is done. Treat string characters as provided, so raw | r'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 type | blob 'abcd' |
| date | convert YYYY-MM-DD formatted string value to a date type | select date '2020-09-28' |
| time | convert HH:MI:SS formatted string value to a time type | select time '18:40:05' |
| time(6) | convert HH:MI:SS.ssssss formatted string value to a time(6) type | select time(6) '18:40:05.123456' |
| time(3) with time zone | convert HH:MI:SS.sss+MI:SS formatted string value to a time(3) with time zone type | select time(3) with time zone '18:40:05.123+02:00' |
| timestamp | convert YYYY-MM-DD HH:MI:SS formatted string value to a timestamp type | select timestamp '2020-09-28 18:40:05' |
| timestamp(6) | convert YYYY-MM-DD HH:MI:SS.ssssss formatted string value to a timestamp(6) type | select timestamp(6) '2020-09-28 18:40:05.123456' |
| timestamp(3) with time zone | convert YYYY-MM-DD HH:MI:SS.sss+MI:SS formatted string value to a timestamp(3) with time zone type | select timestamp(3) with time zone '2020-09-28 18:40:05.123+02:00' |
| interval | convert number value string to second interval type | select interval '67205' second |
| interval | convert number value string to day interval type | select interval '30' day |
| interval | convert number value string to month interval type | select interval '6' month |
| interval | convert HH:MI:SS formatted string value to second interval type | select interval '18:40:05' hour to second |
| interval | convert DD HH:MI:SS formatted string value to second interval type | select interval '2 18:40:05.123' day to second(3) |
| interval | convert YYYY-MM formatted string value to month interval type | select interval '2-5' year to month |
| inet 'valid inet address string' | convert string value to inet type | inet '192.168.1.5/24' |
| json 'valid json string' | convert string value to json type | json '{"a":[1,2,4]}' |
| url 'valid url string' | convert string value to url type | url 'https://www.monetdb.org/Home' |
| uuid 'valid uuid string' | convert string value to uuid type | uuid 'e31960fb-dc8b-452d-ab30-b342723e756a' |