> ## Documentation Index
> Fetch the complete documentation index at: https://docs.kinetica.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Conversion Functions

<a id="sql-conversion-functions" />

<AccordionGroup>
  <Accordion title="CAST (expr AS [SQL_]<conv_type>) *or* CONVERT (expr, [SQL_]<conv_type>)" id="cast-expr-as-sql_<conv_type>-or-convert-expr-sql_<conv_type>" defaultOpen>
    Converts `expr` into `conv_type` data type

    **Conversion Types**:

    <div>
      <table class="table w-full [&_td]:min-w-[150px] [&_th]:text-left [&_td[data-numeric]]:tabular-nums">
        <thead>
          <tr>
            <th>Numeric</th>
            <th>String</th>
            <th>Date/Time</th>
          </tr>
        </thead>

        <tbody>
          <tr>
            <td><code>BIGINT</code></td>
            <td><code>CHAR(N)</code></td>
            <td><code>DATE</code></td>
          </tr>

          <tr>
            <td><code>DECIMAL(P,S)</code></td>
            <td><code>VARCHAR(N)</code></td>
            <td><code>DATETIME</code></td>
          </tr>

          <tr>
            <td><code>DOUBLE</code></td>

            <td />

            <td><code>TIME</code></td>
          </tr>

          <tr>
            <td><code>FLOAT</code></td>

            <td />

            <td><code>TIMESTAMP</code></td>
          </tr>

          <tr>
            <td><code>INTEGER</code></td>

            <td />

            <td><code>TYPE\_DATE</code></td>
          </tr>

          <tr>
            <td><code>NUMERIC(P,S)</code></td>

            <td />

            <td><code>TYPE\_TIME</code></td>
          </tr>

          <tr>
            <td><code>REAL</code></td>

            <td />

            <td><code>TYPE\_TIMESTAMP</code></td>
          </tr>

          <tr>
            <td><code>SMALLINT</code></td>

            <td />

            <td />
          </tr>

          <tr>
            <td><code>TINYINT</code></td>

            <td />

            <td />
          </tr>

          <tr>
            <td><code>UNSIGNED</code> <code>BIGINT</code></td>

            <td />

            <td />
          </tr>
        </tbody>
      </table>
    </div>

    <Info>
      When using the `SQL_` prefix,
      `UNSIGNED BIGINT` becomes `SQL_UNSIGNED_BIGINT`
    </Info>
  </Accordion>

  <Accordion title="CHAR(expr)" id="char-expr" defaultOpen>
    Returns the character associated with the ASCII code
    given in `expr`
  </Accordion>

  <Accordion title="CHAR1(expr)" id="char1-expr" defaultOpen>
    Converts the given `expr` to `VARCHAR(1)` type
  </Accordion>

  <Accordion title="CHAR2(expr)" id="char2-expr" defaultOpen>
    Converts the given `expr` to `VARCHAR(2)` type
  </Accordion>

  <Accordion title="CHAR4(expr)" id="char4-expr" defaultOpen>
    Converts the given `expr` to `VARCHAR(4)` type
  </Accordion>

  <Accordion title="CHAR8(expr)" id="char8-expr" defaultOpen>
    Converts the given `expr` to `VARCHAR(8)` type
  </Accordion>

  <Accordion title="CHAR16(expr)" id="char16-expr" defaultOpen>
    Converts the given `expr` to `VARCHAR(16)` type
  </Accordion>

  <Accordion title="CHAR32(expr)" id="char32-expr" defaultOpen>
    Converts the given `expr` to `VARCHAR(32)` type
  </Accordion>

  <Accordion title="CHAR64(expr)" id="char64-expr" defaultOpen>
    Converts the given `expr` to `VARCHAR(64)` type
  </Accordion>

  <Accordion title="CHAR128(expr)" id="char128-expr" defaultOpen>
    Converts the given `expr` to `VARCHAR(128)` type
  </Accordion>

  <Accordion title="CHAR256(expr)" id="char256-expr" defaultOpen>
    Converts the given `expr` to `VARCHAR(256)` type
  </Accordion>

  <Accordion title="DATE(expr)" id="date-expr" defaultOpen>
    Converts `expr` to date (`YYYY-MM-DD`) format
  </Accordion>

  <Accordion title="DATETIME(expr)" id="datetime-expr" defaultOpen>
    Converts `expr` to datetime (`YYYY-MM-DD HH24:MI:SS.mmm`) format
  </Accordion>

  <Accordion title="DECIMAL(expr)" id="decimal-expr" defaultOpen>
    Converts the given `expr` to `DECIMAL` type
  </Accordion>

  <Accordion title="DOUBLE(expr)" id="double-expr" defaultOpen>
    Converts the given `expr` to `DOUBLE` type
  </Accordion>

  <Accordion title="FLOAT(expr)" id="float-expr" defaultOpen>
    Converts the given `expr` to `REAL` type
  </Accordion>

  <Accordion title="INT(expr)" id="int-expr" defaultOpen>
    Converts the given `expr` to `INTEGER` type
  </Accordion>

  <Accordion title="LONG(expr)" id="long-expr" defaultOpen>
    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
  </Accordion>

  <Accordion title="STRING(expr)" id="string-expr" defaultOpen>
    Converts `expr` to a string format appropriate for the `expr` type
  </Accordion>

  <Accordion title="TIME(expr)" id="time-expr" defaultOpen>
    Converts `expr` to time (`HH24:MI:SS`) format
  </Accordion>

  <Accordion title="TIMESTAMP(expr)" id="timestamp-expr" defaultOpen>
    Converts `expr` to `TIMESTAMP` type
  </Accordion>

  <Accordion title="TO_CHAR(expr, format)" id="to_char-expr-format" defaultOpen>
    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](/content/sql/query/conversion-functions#sql-datetime-conversion-codes) for the list of format codes.

    Example:

    <div>
      <table class="table w-full [&_td]:min-w-[150px] [&_th]:text-left [&_td[data-numeric]]:tabular-nums">
        <tbody>
          <tr>
            <td>**Function Call**</td>
            <td><code>TO\_CHAR(DATETIME(971181296000), '"Last login: "YYYY-MM-DD')</code></td>
          </tr>

          <tr>
            <td>**Return**</td>
            <td><code>Last login: 2000-10-10</code></td>
          </tr>
        </tbody>
      </table>
    </div>
  </Accordion>

  <Accordion title="TO_DATE(string, format)" id="to_date-string-format" defaultOpen>
    Converts the given string to a *date* type with the given format code.
    See [Date/Time Conversion Codes](/content/sql/query/conversion-functions#sql-datetime-conversion-codes) for the list of format codes.

    Example:

    ```
    TO_DATE('2017-06-15', 'YYYY-MM-DD')
    ```
  </Accordion>

  <Accordion title="TO_DATETIME(string, format)" id="to_datetime-string-format" defaultOpen>
    Converts the given string to a *datetime* type with the given format code.
    See [Date/Time Conversion Codes](/content/sql/query/conversion-functions#sql-datetime-conversion-codes) for the list of format codes.

    Example:

    ```
    TO_DATETIME('2017-06-15 10:37:30', 'YYYY-MM-DD HH:MI:SS')
    ```
  </Accordion>

  <Accordion title="TO_TIME(string, format)" id="to_time-string-format" defaultOpen>
    Converts the given string to a *time* type with the given format code.
    See [Date/Time Conversion Codes](/content/sql/query/conversion-functions#sql-datetime-conversion-codes) for the list of format codes.

    Example:

    ```
    TO_TIME('10:37:30', 'HH:MI:SS')
    ```
  </Accordion>

  <Accordion title="TO_TIMESTAMP(string, format)" id="to_timestamp-string-format" defaultOpen>
    Converts the given string to a *timestamp* type with the given format code.
    See [Date/Time Conversion Codes](/content/sql/query/conversion-functions#sql-datetime-conversion-codes) for the list of format codes.

    Example:

    ```
    TO_TIMESTAMP('2017-06-15 10:37:30', 'YYYY-MM-DD HH:MI:SS')
    ```
  </Accordion>

  <Accordion title="ULONG(expr)" id="ulong-expr" defaultOpen>
    Converts the given `expr` to `UNSIGNED BIGINT` type
  </Accordion>

  <Accordion title="VECTOR(expr, len)" id="vector-expr-len" defaultOpen>
    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)
    ```
  </Accordion>
</AccordionGroup>

<a id="sql-datetime-conversion-codes" />

<a id="date-time-conversion-codes" />

## 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: <br /> <br /> \* If the assumed date is greater than 50 years into the future, declare the date to be in the previous century <br /> \* 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 |


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.