SQL Reference

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  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,  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.
SQL
SELECT DOUBLE '1.5';
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.
SQL
SELECT INT[] 'INT[0,1,2,3]';
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.
SQL
SELECT ST_POINT 'POINT(0 0)';
SELECT ST_POINT 'POINT(0 0)';
﻿
Output: POINT(0.0 0.0)
Cast from Date and Time Strings
You can cast date 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.
SQL
SELECT INTERVAL '5 DAYS';
SELECT INTERVAL '5 DAYS';
﻿
Output: 5
Retrieve the interval value 2 from the '2 WEEKS' string.
SQL
SELECT INTERVAL '2 WEEKS';
SELECT INTERVAL '2 WEEKS';
﻿
Output: 2
Retrieve the interval value 10 from the '10 SECONDS' string.
SQL
SELECT INTERVAL '10 SECONDS';
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
SQL
CAST[!] expr [AS] data_type
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 Data Types﻿. 
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.
SQL
SELECT CAST(2 AS VARCHAR);
SELECT CAST(2 AS VARCHAR);
﻿
Output: '2'
Cast a Varchar to Date
Transform the '2020-02-02' string to the DATE data type.
SQL
SELECT CAST('2020-02-02' AS DATE);
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.  
SQL
SELECT CAST!('a' AS INT); 
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
SQL
expr ::[!] data_type
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 Data Types﻿. 
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.
SQL
SELECT '2' :: FLOAT;
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.  
SQL
SELECT 'a' ::! INT;
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
﻿Date / Time Functions﻿﻿
﻿Character / Binary Functions﻿﻿
﻿Math Functions and Operators﻿﻿
﻿Formatting 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 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

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 Data Types.

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 Data Types.

Network Type Functions

Sorted Aggregate Functions