Conversion Functions - Kinetica Docs

CAST (expr AS [SQL_]<conv_type>) *or* CONVERT (expr, [SQL_]<conv_type>)

Converts expr into conv_type data typeConversion Types:

Numeric	String	Date/Time
`BIGINT`	`CHAR(N)`	`DATE`
`DECIMAL(P,S)`	`VARCHAR(N)`	`DATETIME`
`DOUBLE`		`TIME`
`FLOAT`		`TIMESTAMP`
`INTEGER`		`TYPE_DATE`
`NUMERIC(P,S)`		`TYPE_TIME`
`REAL`		`TYPE_TIMESTAMP`
`SMALLINT`
`TINYINT`
`UNSIGNED` `BIGINT`

When using the SQL_ prefix, UNSIGNED BIGINT becomes SQL_UNSIGNED_BIGINT

CHAR(expr)

Returns the character associated with the ASCII code given in expr

CHAR1(expr)

Converts the given expr to VARCHAR(1) type

CHAR2(expr)

Converts the given expr to VARCHAR(2) type

CHAR4(expr)

Converts the given expr to VARCHAR(4) type

CHAR8(expr)

Converts the given expr to VARCHAR(8) type

CHAR16(expr)

Converts the given expr to VARCHAR(16) type

CHAR32(expr)

Converts the given expr to VARCHAR(32) type

CHAR64(expr)

Converts the given expr to VARCHAR(64) type

CHAR128(expr)

Converts the given expr to VARCHAR(128) type

CHAR256(expr)

Converts the given expr to VARCHAR(256) type

DATE(expr)

Converts expr to date (YYYY-MM-DD) format

DATETIME(expr)

Converts expr to datetime (YYYY-MM-DD HH24:MI:SS.mmm) format

DECIMAL(expr)

Converts the given expr to DECIMAL type

DOUBLE(expr)

Converts the given expr to DOUBLE type

FLOAT(expr)

Converts the given expr to REAL type

INT(expr)

Converts the given expr to INTEGER type

LONG(expr)

Converts the given expr to BIGINT type; if expr is a TIMESTAMP or DATETIME type, the value will be the number of milliseconds since the epoch

STRING(expr)

Converts expr to a string format appropriate for the expr type

TIME(expr)

Converts expr to time (HH24:MI:SS) format

TIMESTAMP(expr)

Converts expr to TIMESTAMP type

TO_CHAR(expr, format)

Converts the given date/time expr to a string matching the given format. The returned string will be truncated at 32 characters. See Date/Time Conversion Codes for the list of format codes.Example:

Function Call	`TO_CHAR(DATETIME(971181296000), ‘“Last login: “YYYY-MM-DD’)`
Return	`Last login: 2000-10-10`

TO_DATE(string, format)

Converts the given string to a date type with the given format code. See Date/Time Conversion Codes for the list of format codes.Example:

TO_DATE('2017-06-15', 'YYYY-MM-DD')

TO_DATETIME(string, format)

Converts the given string to a datetime type with the given format code. See Date/Time Conversion Codes for the list of format codes.Example:

TO_DATETIME('2017-06-15 10:37:30', 'YYYY-MM-DD HH:MI:SS')

TO_TIME(string, format)

Converts the given string to a time type with the given format code. See Date/Time Conversion Codes for the list of format codes.Example:

TO_TIME('10:37:30', 'HH:MI:SS')

TO_TIMESTAMP(string, format)

Converts the given string to a timestamp type with the given format code. See Date/Time Conversion Codes for the list of format codes.Example:

TO_TIMESTAMP('2017-06-15 10:37:30', 'YYYY-MM-DD HH:MI:SS')

ULONG(expr)

Converts the given expr to UNSIGNED BIGINT type

VECTOR(expr, len)

Converts the given stringified array of len number of float values in expr to VECTOR type.Example:

VECTOR('[1.1, 2.2, 3.3]', 3)

Date/Time Conversion Codes

The following formatting codes can be used to convert date/time strings to native date/time objects (with TO_DATE, TO_DATETIME, TO_TIME and TO_TIMESTAMP) and for formatting date/time objects as strings (with TO_CHAR). These characters are interpreted literally: - / , . ; : Other characters need to be quoted in order to be interpreted literally. For example, MM-DD-YYYY can be used to convert 01-02-2022 to a date, while "Today is "MM-DD-YYYY is needed to convert Today is 01-02-2022. In the following table, Opt in the Input column indicates that the field may be empty when being read, and still be considered a valid input. This can be useful when reading data from a source that, for example, sometimes includes milliseconds in their time strings and sometimes does not (e.g., the MMM code is Opt). Yes in the Input column indicates that some data must be present in the input string to be considered valid, unless it is after a $ character, in which case all input after the $ is optional.

Format Code	Input	Output	Description
`_`	Yes		Any one character
`?`	Opt		Zero or more non-digit characters
`$`	Yes		Everything after this is optional; e.g., `HH:MI$:SS` would accept `12:34` and `12:34:56`
`AD`		Yes	Era indicator without periods
`A.D.`		Yes	Era indicator with periods
`AM`	Yes	Yes	Meridian indicator without periods [`AM`, `PM`]
`A.M.`	Yes	Yes	Meridian indicator with periods [`A.M.`, `P.M.`]
`BC`		Yes	Era indicator without periods
`B.C.`		Yes	Era indicator with periods
`C`	Yes	Yes	Number of day in calendar month, with optional leading zero [`1` - `31`]
`CC`		Yes	Century (if last 2 digits of the 4-digit year are `00`, this is the first 2 digits; otherwise, this is first 2 digits + 1)
`D`	Yes	Yes	Day of week [`1` - `7`] (Sunday - Saturday)
`DAY`	Yes	Yes	Day of week [`Sunday` - `Saturday`]
`DD`	Yes	Yes	Number of day in month, with leading zero [`01` - `31`]
`DDD`	Yes	Yes	Number of day of year [`1` - `366`]
`DL`	Yes	Yes	Date long format (`DAY, MONTH C, RRRR`)
`DS`	Yes	Yes	Date short format (`M/C/RRRR`)
`DY`	Yes	Yes	Abbreviated day name (e.g., “Mon”)
`FF[1-9]`	Opt	Yes	Fractional seconds where the number of fractional second digits can be specified (default is 3 digits); when used for input, the string may contain an optional ”.” preceding the numbers
`H`	Yes	Yes	Alias for `H12`
`H12`	Yes	Yes	Hour of day in 12-hour format, with optional leading zero [`0` - `11`]
`H24`	Yes	Yes	Hour of day in 24-hour format, with optional leading zero [`0` - `23`]
`HH`	Yes	Yes	Alias for `HH12`
`HH12`	Yes	Yes	Hour of day in 12-hour format, with leading zero [`00` - `11`]
`HH24`	Yes	Yes	Hour of day in 24-hour format, with leading zero [`00` - `23`]
`I`	Yes	Yes	Last digit of ISO year
`ID`	Yes	Yes	ISO 8601 day of week [`1` - `7`] (Monday - Sunday)
`IDDD`	Yes	Yes	ISO 8601 day of year [`001` - `371`] (where `001` is the Monday of the 1st ISO week)
`IW`	Yes	Yes	ISO 8601 week of year [`01` - `53`] (where `01` contains the first Thursday of the year)
`IY`	Yes	Yes	Last 2 digits of ISO year
`IYY`	Yes	Yes	Last 3 digits of ISO year
`IYYY`	Yes	Yes	Last 4 digits of ISO year
`J`	Yes	Yes	Julian day; the number of days since January 1, 4712 BC. Number specified with `J` must be integers.
`LLL`	Opt	Yes	Whole milliseconds (often positioned after a colon); e.g., `12:34:56:78` => 78 milliseconds; when used for input, the string may contain an optional ”.” or ”:” preceding the numbers; when ”.” is found, this code will act like the `MMM` code
`M`	Yes	Yes	Number of month in year, with optional leading zero [`1` - `12`]
`MI`	Yes	Yes	Minute of hour [`00` - `59`]
`MM`	Yes	Yes	Number of month in year, with leading zero [`01` - `12`]
`MMM`	Opt	Yes	Fractions of a second up to milliseconds; e.g., `12:34:56.78` => 780 milliseconds; when used for input, the string may contain an optional ”.” or ”:” preceding the numbers; when ”:” is found, this code will act like the `LLL` code
`MON`	Yes	Yes	3-character abbreviation of month in title case [`Jan` - `Dec`]
`MONTH`	Yes	Yes	Full name of month [`January` - `December`]
`MS`	Opt	Yes	Alias for `MMM`
`OF`	Opt	Yes	Time-zone offset from UTC (`TZHTZM`)
`PM`	Yes	Yes	Alias for `AM`
`P.M.`	Yes	Yes	Alias for `A.M.`
`Q`		Yes	Quarter of the year [`1` - `4`] (January-March - October-December)
`RM`	Yes	Yes	Roman numeral month [`I` - `XII`] (January - December)
`RR`	Yes	Yes	Rounded year. Assume the given 2-digit year occurs in the present century, then: * If the assumed date is greater than 50 years into the future, declare the date to be in the previous century * If the assumed date is greater than 50 years into the past, declare the date to be in the next century
`RRRR`	Yes	Yes	Either a 2-digit or 4-digit year; if 2-digit, equivalent to `RR`
`SCC`		Yes	Same as `CC`
`SS`	Yes	Yes	Second of minute [`00` - `59`]
`SSSS`	Yes	Yes	Alias for `SSSSS`
`SSSSS`	Yes	Yes	Seconds past midnight [`0` - `86399`]
`SYEAR`		Yes	Same as `YEAR`
`SYYYY`	Yes	Yes	Same as `YYYY`
`TS`	Yes	Yes	Time short format (`H24:MI:SS.MMM`)
`TZ`	Opt	Yes	Time zone (`TZH`)
`TZH`	Opt	Yes	Time zone hour (ISO); “Z” or +/- followed by one or two digits (will always output “Z”)
`TZM`	Opt	Yes	Time zone minute (ISO); colon followed by one or two digits; for input, an optional colon is allowed at the front; if present, two digits must follow it
`US`	Opt	Yes	Microseconds; when used for input, the string may contain an optional ”.” preceding the numbers
`W`	Yes	Yes	Week of month [`1` - `5`], where week 1 starts on the first day of the month and ends on the seventh
`WW`	Yes	Yes	Week of year [`1` - `53`], where week 1 starts on the first day of the year and continues to the seventh day of the year
`X`	Yes	Yes	Local radix character (always `.`. E.g., `HH:MI:SSXFF`)
`Y`	Yes	Yes	Last 1 digit of year
`Y,YYY`	Yes	Yes	Year with a comma after the thousands place
`YEAR`		Yes	Year spelled out (e.g., “Two Thousand Twenty Five”)
`YY`	Yes	Yes	Last 2 digits of year
`YYY`	Yes	Yes	Last 3 digits of year
`YYYY`	Yes	Yes	4-digit year

​Date/Time Conversion Codes

Date/Time Conversion Codes