Scalar Data Conversion Functions

these functions convert one data type to another scalar data conversion functions function syntax purpose byte byte(numeric value) casts the argument to a value of type byte byte(character value) parses the string as a byte value smallint smallint(numeric value) casts the argument to a value of type smallint smallint(character value) parses the string as a smallint value integer int(numeric value) or integer(numeric value) casts the argument to a value of type integer int(character value) or integer(character value) parses the string as an integer value bigint bigint(numeric value) casts the argument to a value of type bigint bigint(character value) parses the string as a bigint value bigint(time value) creates a bigint with the number of milliseconds represented by the time argument bigint(timestamp value) creates a bigint with the number of milliseconds after the {{unix}} epoch corresponding to the timestamp argument float float(numeric value) casts the argument to a value of type float float(character value) parses the string as a float value double double(numeric value) casts the argument to a value of type double double(character value) parses the string as a double value decimal decimal(numeric value) casts the argument to a value of type decimal decimal(character value) parses the string and creates a decimal value decimal(numeric value, integer value, integer value) casts the first argument to a decimal value with precision of the second argument and scale of the third argument decimal(character value, integer value, integer value) parses the first argument to a decimal value with precision of the second argument and scale of the third argument char | str char(numeric value) creates a string version of the numeric value char(character value) none char(timestamp) creates a string representation of the timestamp char(date) creates a string representation of the date char(time) creates a string representation of the time char(boolean) creates a string representation of the boolean value char(binary) creates a hexadecimal string representation of the binary value char(ip) creates a string representation of the ipv4 or ipv6 value char(ipv4) creates a string representation of the ipv4 value char(hash) creates a string representation of the fixed binary hash value char(point) creates a string representation of the point value char(uuid) creates a string representation of the universally unique identifier (uuid) binary binary(character value) parses a hexadecimal string (such as 0x54ab ) to create a binary value letters can be of either case binary(binary) none binary(hash) converts the fixed length binary value (hash) to a variable length binary value boolean boolean(character value) parses a string to create a boolean value the string must contain either true or false it is case insensitive boolean(boolean) none date date(character value) parses a string in the form 'yyyy mm dd' to create a date extra characters are ignored date(date) none date(timestamp) truncates a timestamp to be a date value hash hash(char, length) creates a fixed length binary value, with length \<length>, from a string, i e 0x1234abcd zero extended if the string does not have enough bytes, truncated if it has too many hash(binary, length) creates a fixed length binary value, with length \<length>, from a variable length binary value zero extended if the string does not have enough bytes, truncated if it has too many ip ip(character value) creates a value of type ipv4 or ipv6 from a string ipv4 ipv4(character value) parses a string containing an ip address and creates a value of type ipv4 ipv4(ipv4) no op time time(character value) parses the string to create a time value the string must be in the form 'hh\ mm\[ sssssssss]' time(bigint) creates a time value representing a certain number of milliseconds after midnight time(time) none time(timestamp) makes a time value from the time component of a timestamp timestamp timestamp(character value) parses a string and makes a timestamp value the string must be in the format 'yyyy mm dd\[ hh\ mm]\[ sssssssss]' timestamp(date) makes a timestamp from the date by setting the time component to 00 00 00 0 timestamp(timestamp) none timestamp(bigint value) creates a timestamp from a bigint representing the number of milliseconds after the unix epoch (1/1/1970) uuid uuid(character value) parses the string and makes a uuid value the string must be a valid uuid uuid(uuid) none weeks weeks(integral value) converts an integral value to an interval value of type weeks for date calculations weeks(weeks) none days days(integral value) converts an integral value to an interval value of type days for date calculations days(days) none days(weeks) converts an interval value of type weeks to an interval value of type days representing the same time span months months(integral value) converts an integral value to an interval value of type months for date calculations months(months) none months(years) converts an interval value of type years to an interval value of type months representing the same time span years years(integral value) converts an integral value to an interval value of type years years(years) none hours hours(integral value) converts an integral value to an interval value of type hours hours(hours) none hours(days) converts an interval value of type days to an interval value of type hours representing the same time span hours(weeks) converts an interval value of type weeks to an interval value of type hours representing the same time span minutes minutes(integral value) converts an integral value to an interval value of type minutes minutes(minutes) none minutes(hours) converts an interval value of type hours to an interval value of type minutes representing the same time span minutes(days) converts an interval value of type days to an interval value of type minutes representing the same time span minutes(weeks) converts an interval value of type weeks to an interval value of type minutes representing the same time span seconds seconds(integral value) converts an integral value to an interval value of type seconds seconds(seconds) none seconds(minutes) converts an interval value of type minutes to an interval value of type seconds representing the same time span seconds(hours) converts an interval value of type hours to an interval value of type seconds representing the same time span seconds(days) converts an interval value of type days to an interval value of type seconds representing the same time span seconds(weeks) converts an interval value of type weeks to an interval value of type seconds representing the same time span milliseconds milliseconds(integral value) converts an integral value to an interval value of type milliseconds milliseconds(milliseconds) none milliseconds(seconds) converts an interval value of type seconds to an interval value of type milliseconds, representing the same time span milliseconds(minutes) converts an interval value of type minutes to an interval value of type milliseconds representing the same time span milliseconds(hours) converts an interval value of type hours to an interval value of type milliseconds representing the same time span milliseconds(days) converts an interval value of type days to an interval value of type milliseconds representing the same time span milliseconds(weeks) converts an interval value of type weeks to an interval value of type milliseconds representing the same time span microseconds microseconds(integral value) converts an integral value to an interval value of type microseconds microseconds(milliseconds) converts an interval value of type milliseconds to an interval value of type microseconds representing the same time span microseconds(seconds) converts an interval value of type seconds to an interval value of type microseconds representing the same time span microseconds(minutes) converts an interval value of type minutes to an interval value of type microseconds representing the same time span microseconds(hours) converts an interval value of type hours to an interval value of type microseconds representing the same time span microseconds(days) converts an interval value of type days to an interval value of type microseconds representing the same time span microseconds(weeks) converts an interval value of type weeks to an interval value of type microseconds representing the same time span microseconds(microseconds) none nanoseconds nanoseconds(integral value) converts an integral value to an interval value of type nanoseconds nanoseconds(microseconds) converts an interval value of type microseconds to an interval value of type nanoseconds representing the same time span nanoseconds(milliseconds) converts an interval value of type milliseconds to an interval value of type nanoseconds representing the same time span nanoseconds(seconds) converts an interval value of type seconds to an interval value of type nanoseconds representing the same time span nanoseconds(minutes) converts an interval value of type minutes to an interval value of type nanoseconds representing the same time span nanoseconds(hours) converts an interval value of type hours to an interval value of type nanoseconds representing the same time span nanoseconds(days) converts an interval value of type days to an interval value of type nanoseconds representing the same time span nanoseconds(weeks) converts an interval value of type weeks to an interval value of type nanoseconds representing the same time span nanoseconds(nanoseconds) none implicit conversions in certain situations, {{ocient}} sql can automatically convert expressions to a specific data type without an explicit sql statement cast from strings you can cast string literals to any other data type by placing the name of the data type before the string literal examples cast a double cast the value 1 5 from a string to a floating point value select double '1 5'; output 1 5 cast an array cast the array int\[0,1,2,3] from a string to an array of integers select int\[] 'int\[0,1,2,3]'; output \['0','1','2','3'] cast a point value cast the point value point(0 0) to the geospatial data type point in this case, the cast keyword is st point , and the data type is point select st point 'point(0 0)'; output point(0 0 0 0) cast from date and time strings you can cast d ate and time values to their interval value with the interval keyword retrieve interval values from various date and time strings retrieve the interval value 5 from the '5 days' string select interval '5 days'; output 5 retrieve the interval value 2 from the '2 weeks' string select interval '2 weeks'; output 2 retrieve the interval value 10 from the '10 seconds' string select interval '10 seconds'; output 10 cast functions these sql statements allow casting an expression to any other data type cast transforms an expression to any other data type this function operates similarly to the /# operator syntax cast\[!] expr \[as] data type argument data type description expr any a column or value to transform to another data type data type string any of the ocient supported data types for a full list, see docid\ ogtviwl gtbgv0chhrh 3 as shown in the syntax examples, cast can support the optional ! operator the ! operator returns a null value if the operation cannot cast to the specified data type this behavior prevents situations where the system would normally raise an error examples cast an integer to varchar transform the value 2 as a varchar string select cast(2 as varchar); output '2' cast a varchar to date transform the '2020 02 02' string to the date data type select cast('2020 02 02' as date); output '2020 02 02' cast an incompatible type with the ! operator transform the 'a' string to an integer, or int data type, using the ! operator select cast!('a' as int); output null this operation generates null because the database cannot transform this value to an integer without adding this operator, the statement raises an error because the database cannot transform the input expression to a numeric type \ operator transforms an expression to any other data type this function operates similarly to the /#cast function \ syntax expr \[!] data type argument data type description expr any a column or value to transform to another data type data type string any of the ocient supported data types for a full list, see docid\ ogtviwl gtbgv0chhrh 3 as shown in the syntax examples, can support the optional ! operator the ! operator returns a null value if the operation cannot cast to the specified data type this behavior prevents situations where the system would normally raise an error examples cast an integer to float transform the '2' string to a floating point number select '2' float; output 2 0 cast to an incompatible type with the ! operator transform the 'a' string to an integer, or int data type, using the ! operator select 'a' ! int; output null this operation generates null because the database cannot transform this value to an integer without adding this operator, the statement raises an error because the database cannot transform the input expression to a numeric type related links docid\ zcon ufstf4uhc5airgpg docid\ kolkdboplprrwxs3g6gsh docid\ e2c2vckzh8dfsbp1wngpr docid\ u47hjysi1oojk6eg2kjut