Conversion functions

Change a value from one type to another with CAST, ::, to_timestamp and to_date.

• Category: Functions — conversion
• Keywords: convert, conversion, type conversion, change type, cast, ::, to number, to text, parse number, parse date, epoch, unix time, milliseconds, cannot convert, INTEGER, BIGINT, BOOLEAN, VARCHAR, TEXT, JSONB, BYTEA, BLOB, FLOAT, SHORT, BYTE
• See also: Column types, Date and time functions (to_char), Conditional and NULL functions (coalesce), Math functions (round), JSON functions, Operators

A value has a type: a number, a text, a time and so on. Sometimes a value has the wrong type for what you want to do. A price read from a text file is the text '512.50', not a number. CAST changes the type.

• CAST(x AS type) and x::type do the same thing.
• to_timestamp(x) turns text or seconds into a time; to_date(x) turns text or milliseconds into a date.
• KDB never guesses. When a value does not fit the new type, you get an error like cannot convert '5.5' to INT. It does not round or cut the value for you.
• NULL stays NULL in every conversion.

The rules are the same as when a value goes into a table column of that type (see column types). The entries "Into ..." below list them for each type.

Example: Prices, quantities and times that arrived as text

Setup:

CREATE TABLE price_import (line INT PRIMARY KEY, symbol STRING, price STRING, qty STRING, traded STRING, is_buy STRING);
INSERT INTO price_import VALUES
  (1, 'CITY', '512.50', '100', '2026-09-10T11:05:00+05:45', 'true'),
  (2, 'COAST',  '398',    '20',  '2026-09-10T13:00:00+05:45', 'false'),
  (3, 'VOLT', '184.0',  '50',  '2026-09-10T14:59:00+05:45', '1');

SELECT symbol,
       CAST(price AS DOUBLE) * CAST(qty AS LONG) AS amount,
       CAST(traded AS TIMESTAMP) AS traded_utc,
       is_buy::BOOL AS is_buy
FROM price_import
ORDER BY line;

Result:

(3 rows)

The examples below also use this small accounting table. Money is kept in DECIMAL(2): an exact number with 2 places after the point.

Setup used by the examples below:

CREATE TABLE ledger (
  id      INT PRIMARY KEY,
  day     DATE,
  account STRING,
  amount  DECIMAL(2)
);
INSERT INTO ledger VALUES
  (1, '2026-09-01', 'Sales', 1250.00),
  (2, '2026-09-02', 'Sales', 899.99),
  (3, '2026-09-02', 'Rent', 1200.50);

CAST

Syntax:

CAST(value AS type)

Changes value into type.

• Returns: a value of type.
• NULL: CAST(NULL AS type) is NULL.
• Notes:
  • When the value does not fit, the error is cannot convert '...' to TYPE. For JSON and BYTES the message also says what is wrong.
  • DECIMAL without a scale means DOUBLE. Write CAST(x AS DECIMAL(2)) when you want exact money. A length like VARCHAR(10) or CHAR(3) is read and ignored: nothing is cut.
  • Many things need no CAST. Text compared with a time is read as a time (time >= '2026-09-01'). || joins numbers as text ('CITY' || 1).
  • To turn a time into formatted text, use to_char, not CAST.

Example

SELECT CAST('512.5' AS DOUBLE) + 1 AS a,
       CAST(42 AS STRING) AS b,
       CAST('2026-09-10T11:00:00+05:45' AS TIMESTAMP) AS c,
       CAST('12.5' AS DECIMAL(2)) AS d,
       CAST(NULL AS INT) AS e;

Result:

(1 row)

Example: 13% VAT on each ledger line, as exact money

SELECT id, account, amount,
       CAST(round(amount * 0.13, 2) AS DECIMAL(2)) AS vat,
       amount * 0.13 AS vat_as_double
FROM ledger
ORDER BY id;

Result:

(3 rows)

Example: Into a whole number: the fraction is rounded

SELECT CAST(5.5 AS INT) AS half_up, CAST(5.4 AS INT) AS down, CAST(-5.5 AS INT) AS negative;

Result:

(1 row)

Example: DECIMAL without a scale is DOUBLE

SELECT CAST('12.50' AS DECIMAL) AS as_double, CAST('12.50' AS DECIMAL(2)) AS exact_money;

Result:

(1 row)

:: (short cast)

Syntax:

value::type

The short way to write CAST(value AS type). '42'::INT is CAST('42' AS INT). It comes from PostgreSQL.

• Returns: a value of type, as with CAST.
• NULL: NULL::INT is NULL.
• Notes: :: holds on to the value right before it, more tightly than any operator. 2 + 3::DOUBLE changes only 3. Put ( ) around a longer expression: (2 + 3)::STRING.

Example

SELECT '42'::INT + 1 AS a, 2 + 3::DOUBLE AS b, (2 + 3)::STRING AS c, '12.5'::DECIMAL(2) AS d, NULL::BOOL AS e;

Result:

(1 row)

Example: Filter candles with a Nepal time written as text (sample data: sample)

SELECT time, symbol, close
FROM ochl_minutes
WHERE symbol = 'COAST'
  AND time >= '2026-09-10T11:00:00+05:45'::TIMESTAMP
  AND time <  '2026-09-10T11:03:00+05:45'::TIMESTAMP;

Result:

(3 rows)

Example: A length after VARCHAR is read and ignored

SELECT CAST('CITY' AS VARCHAR(10)) AS a, CAST('CITY' AS VARCHAR(3)) AS nothing_is_cut;

Result:

(1 row)

to_timestamp

Syntax:

to_timestamp(value)

Makes a TIMESTAMP (a moment in time, stored in UTC) from time text, a DATE or a number of seconds.

• Returns: TIMESTAMP.
• NULL: NULL in, NULL out.
• Notes:
  • A number means seconds, as in PostgreSQL and Unix time: to_timestamp(1789020900). A fraction keeps the milliseconds: to_timestamp(1789020900.25). Before 2026-09-25 a number meant milliseconds.
  • Milliseconds, like JavaScript's Date.now(): divide by 1000.0 first, or cast: CAST(ms AS TIMESTAMP).
  • There is no second "format" argument. The text must look like YYYY-MM-DD, then optional T or space and HH:MM:SS.fff, then optional Z or +05:45. Text without a zone is UTC.
  • A DATE becomes midnight UTC of that day.

Example

SELECT to_timestamp('2026-09-10T11:00:00+05:45') AS nepal_text,
       to_timestamp('2026-09-10') AS date_text,
       to_timestamp(1789020900) AS from_seconds,
       to_timestamp(1789020900.25) AS with_fraction,
       to_timestamp(1789020900000 / 1000.0) AS from_ms,
       to_timestamp(NULL) AS nothing;

Result:

(1 row)

Example: App sign-up times sent as milliseconds

Setup:

CREATE TABLE app_signups (user_id INT PRIMARY KEY, name STRING, created_ms LONG);
INSERT INTO app_signups VALUES (1, 'Sita', 1788900300000), (2, 'Ram', 1788928200000), (3, 'Hari', 1789020900000);

SELECT user_id, name, created_ms, to_timestamp(created_ms / 1000.0) AS created, CAST(created_ms AS TIMESTAMP) AS same
FROM app_signups
ORDER BY user_id;

Result:

(3 rows)

Example: Text that is not a time (shows an error)

SELECT to_timestamp('10/09/2026');

Result:

Error: line 1, column 8: to_timestamp: argument 1 must be a time, got '10/09/2026'

to_date

Syntax:

to_date(value)

Makes a DATE (a day without a clock time) from time text, a TIMESTAMP or a number of milliseconds. It keeps the UTC date.

• Returns: DATE.
• NULL: NULL in, NULL out.
• Notes:
  • The date is the UTC date. Nepal is 5 hours 45 minutes ahead of UTC, so between 00:00 and 05:45 Nepal time the UTC date is the day before. For the Nepal date write to_date(time + 20700000) (20700000 ms = 5 h 45 min).
  • to_date(86400000) works (a number of milliseconds), but CAST(86400000 AS DATE) is an error. CAST into DATE does not take numbers.

Example

SELECT to_date('2026-09-10T20:00:00Z') AS a,
       to_date('2026-09-10T03:00:00+05:45') AS nepal_3am_is_utc_day_before,
       to_date(86400000) AS b,
       to_date(NULL) AS c;

Result:

(1 row)

Example: Sign-ups per day: UTC date and Nepal date

Setup:

CREATE TABLE app_signups (user_id INT PRIMARY KEY, name STRING, created TIMESTAMP);
INSERT INTO app_signups VALUES
  (1, 'Sita', '2026-09-09T02:30:00+05:45'),
  (2, 'Ram',  '2026-09-09T10:15:00+05:45'),
  (3, 'Hari', '2026-09-09T15:45:00+05:45'),
  (4, 'Gita', '2026-09-10T12:00:00+05:45');

SELECT to_date(created) AS utc_day, count(*) AS signups
FROM app_signups GROUP BY to_date(created) ORDER BY utc_day;

SELECT to_date(created + 20700000) AS nepal_day, count(*) AS signups
FROM app_signups GROUP BY to_date(created + 20700000) ORDER BY nepal_day;

Result:

SELECT to_date(created) AS utc_day, count(*) AS signups FROM app_signups GROUP BY to_date(created) O

(3 rows)

SELECT to_date(created + 20700000) AS nepal_day, count(*) AS signups FROM app_signups GROUP BY to_da

(2 rows)

Example: true is not a time (shows an error)

SELECT to_date(TRUE);

Result:

Error: line 1, column 8: to_date: argument 1 must be a time, got 'true'

Into integers

Syntax:

CAST(value AS BYTE | SHORT | INT | INTEGER | LONG | BIGINT)

Whole numbers. The types differ only in size:

• Returns: the chosen integer type.
• NULL: stays NULL.
• Notes:
  • A fraction is rounded to the nearest whole number, half away from zero, as PostgreSQL does. To cut instead, use floor, ceil or trunc first.
  • A number too big for the type is still an error: CAST(300 AS BYTE).
  • For the milliseconds of a time, CAST(t AS LONG) works; time - 0::TIMESTAMP does the same.

Example

SELECT CAST('42' AS INT) AS a, CAST(5.0 AS INT) AS b, CAST(TRUE AS INT) AS c,
       CAST(CAST('2.00' AS DECIMAL(2)) AS LONG) AS d, CAST(round(512.5) AS INT) AS e, CAST(-127 AS BYTE) AS f;

Result:

(1 row)

Example: Shares per trade from text, and a time as milliseconds (sample data: sample)

SELECT transaction_id, symbol,
       CAST(quantity AS STRING) AS qty_text,
       CAST(CAST(quantity AS STRING) AS LONG) + 1 AS back_to_number,
       date - 0::TIMESTAMP AS date_ms
FROM trade_log
ORDER BY transaction_id
LIMIT 3;

Result:

(3 rows)

Example: A fraction is rounded to the nearest whole number

SELECT CAST(512.5 AS INT) AS a, CAST(512.4 AS INT) AS b, CAST('5.5' AS INT) AS from_text;

Result:

(1 row)

Example: Too big for BYTE (shows an error)

SELECT CAST(300 AS BYTE);

Result:

Error: line 1, column 8: cannot convert '300' to BYTE

Example: A TIMESTAMP gives its milliseconds; a DATE does not (shows an error)

SELECT CAST(CAST('2026-09-10' AS TIMESTAMP) AS LONG) AS milliseconds;
SELECT CAST(CAST('2026-09-10' AS DATE) AS LONG);

Result:

(1 row)

Error: line 2, column 8: cannot convert '2026-09-10' to LONG

Into DOUBLE and FLOAT

Syntax:

CAST(value AS DOUBLE | FLOAT)

Numbers with a fraction. DOUBLE keeps about 15 digits. FLOAT is smaller and keeps about 7 digits.

• Returns: DOUBLE or FLOAT.
• NULL: stays NULL.
• Notes:
  • A DOUBLE cannot hold every decimal exactly. 0.1 + 0.2 is shown as 0.3, but the real value is 0.30000000000000004 (see Into STRING). For money use DECIMAL(2).
  • FLOAT loses digits: CAST(0.1 AS FLOAT) shows 0.100000001490116. A value above about 3.4e38 is an error.
  • / already gives a DOUBLE (7 / 2 is 3.5), so CAST(7 AS DOUBLE) / 2 is not needed (it is after SET int_division = integer: integer division).

Example

SELECT CAST('512.5' AS DOUBLE) AS a, CAST('1e3' AS DOUBLE) AS b,
       CAST(CAST('1.25' AS DECIMAL(2)) AS DOUBLE) AS c, CAST(0.1 AS FLOAT) AS d, 0.1 + 0.2 AS e;

Result:

(1 row)

Example: Ledger amounts as DOUBLE for a chart

SELECT day, account, amount, CAST(amount AS DOUBLE) AS amount_double
FROM ledger
ORDER BY id;

Result:

(3 rows)

Example: Text that is not a number (shows an error)

SELECT CAST('$512' AS DOUBLE);

Result:

Error: line 1, column 8: cannot convert '$512' to DOUBLE

Into DECIMAL(s)

Syntax:

CAST(value AS DECIMAL(scale))

An exact number with scale places after the point. DECIMAL(2) is right for dollars and cents.

• Returns: DECIMAL(scale).
• NULL: stays NULL.
• Notes:
  • More places than scale are rounded to the nearest, half away from zero. CAST(round(x, 2) AS DECIMAL(2)) says the same thing and is clearer to read.
  • DECIMAL without a scale is not exact money: it is the same as DOUBLE.
  • Arithmetic with DECIMAL values (amount * 0.13, debit - credit) is exact and gives a DECIMAL, as in PostgreSQL (DECIMAL). CAST it to DECIMAL(2) to show it with 2 places. sum and avg of DECIMAL values are DECIMAL too (sum).
  • A DOUBLE like 0.1 + 0.2 is close enough to 0.30, so CAST(0.1 + 0.2 AS DECIMAL(2)) gives 0.30.
  • DECIMAL(s) keeps 18 digits in 64 bits: with 2 places the largest value is 92233720368547758.07. NUMERIC(p, s) keeps its p digits, up to 38: CAST(x AS NUMERIC(24,2)) holds 22 digits before the point, and more is an error that names the type you wrote: cannot convert '...' to NUMERIC(24,2): numeric field overflow.

Example

SELECT CAST(7 AS DECIMAL(2)) AS a, CAST('1250.75' AS DECIMAL(2)) AS b, CAST(1500.256 AS DECIMAL(3)) AS c,
       CAST(0.1 + 0.2 AS DECIMAL(2)) AS d, CAST(CAST('1.5' AS DECIMAL(1)) AS DECIMAL(3)) AS e;

Result:

(1 row)

Example: Month report: totals and average per account, exact to the cent

SELECT account,
       sum(amount) AS total,
       CAST(round(avg(amount), 2) AS DECIMAL(2)) AS average,
       count(*) AS lines
FROM ledger
WHERE day IN '2026-09'
GROUP BY account
ORDER BY account;

Result:

(2 rows)

Example: Too many places are rounded, not refused

SELECT CAST(12.345 AS DECIMAL(2)) AS rounded_up, CAST(12.344 AS DECIMAL(2)) AS rounded_down,
       CAST('1250.756' AS DECIMAL(2)) AS from_text;

Result:

(1 row)

Example: NUMERIC(24,2) holds 24 digits

SELECT CAST(123456789012345678.12 AS NUMERIC(24,2)) AS big, CAST(1 AS NUMERIC(20,2)) / 3 AS third;

Result:

(1 row)

Into TIMESTAMP

Syntax:

CAST(value AS TIMESTAMP)

A moment in time, stored as milliseconds since 1970-01-01 UTC. KDB always shows it in UTC, like 2026-09-10T05:15:00.000Z.

• Returns: TIMESTAMP.
• NULL: stays NULL.
• Notes:
  • A local time must be written with its zone offset, for example +05:45 for Nepal. '2026-09-10 11:00' is 11:00 UTC, which is 16:45 in Nepal.
  • Zone names (NPT, Asia/Kathmandu) are not allowed inside the text. Only an offset like +05:45.
  • Other date orders ('10/09/2026'), one-digit months ('2026-9-1') and 24:00 are errors. More than 3 digits after the second are cut to milliseconds.
  • The same as to_timestamp.

Example

SELECT CAST('2026-09-10T11:00:00+05:45' AS TIMESTAMP) AS nepal_11am,
       CAST('2026-09-10 11:00' AS TIMESTAMP) AS utc_11am,
       CAST(CAST('2026-09-10' AS DATE) AS TIMESTAMP) AS from_date,
       CAST(0 AS TIMESTAMP) AS zero,
       CAST('2026-09-10T11:00:00.123456+0545' AS TIMESTAMP) AS micro_cut;

Result:

(1 row)

Example: Market open and close (Nepal time) as UTC times (sample data: sample)

SELECT symbol, count(*) AS minutes, min(time) AS first_minute, max(time) AS last_minute
FROM ochl_minutes
WHERE time >= CAST('2026-09-10T11:00:00+05:45' AS TIMESTAMP)
  AND time <  CAST('2026-09-10T15:00:00+05:45' AS TIMESTAMP)
GROUP BY symbol
ORDER BY symbol;

Result:

(2 rows)

Example: Day/month/year order is not read (shows an error)

SELECT CAST('10/09/2026' AS TIMESTAMP);

Result:

Error: line 1, column 8: cannot convert '10/09/2026' to TIMESTAMP

Into DATE

Syntax:

CAST(value AS DATE)

A day without a clock time, shown like 2026-09-10.

• Returns: DATE.
• NULL: stays NULL.
• Notes:
  • '2026-09-10T03:00:00+05:45' is 21:15 UTC on 9 September, so its DATE is 2026-09-09.
  • A date that does not exist ('2026-02-30') is an error.
  • DATE + 1 is the next day.

Example

SELECT CAST('2026-09-10' AS DATE) AS a,
       CAST('2026-09-10T03:00:00+05:45' AS DATE) AS nepal_early_morning,
       CAST(CAST('2026-09-10T23:59:00Z' AS TIMESTAMP) AS DATE) AS b,
       CAST('2026-09-10' AS DATE) + 1 AS next_day;

Result:

(1 row)

Example: Trades per day from the trade log (sample data: sample)

SELECT CAST(date AS DATE) AS trade_day, count(*) AS trades, sum(quantity) AS shares
FROM trade_log
GROUP BY CAST(date AS DATE)
ORDER BY trade_day;

Result:

(2 rows)

Example: There is no 30 February (shows an error)

SELECT CAST('2026-02-30' AS DATE);

Result:

Error: line 1, column 8: cannot convert '2026-02-30' to DATE

Example: CAST into DATE does not take a number (shows an error)

SELECT CAST(20260910 AS DATE);

Result:

Error: line 1, column 8: cannot convert '20260910' to DATE

Into BOOL

Syntax:

CAST(value AS BOOL | BOOLEAN)

true or false.

• Returns: BOOL.
• NULL: stays NULL.
• Notes: 'yes', 'no', 2 and 1.0 are errors. Map them with CASE first.

Example

SELECT CAST(1 AS BOOL) AS a, CAST(0 AS BOOL) AS b, CAST('TRUE' AS BOOL) AS c, CAST('f' AS BOOL) AS d, '1'::BOOLEAN AS e;

Result:

(1 row)

Example: A yes/no column from an import (shows an error)

SELECT CAST('yes' AS BOOL);

Result:

Error: line 1, column 8: cannot convert 'yes' to BOOL

Example: Map yes/no with CASE, then it works

SELECT answer, CASE lower(answer) WHEN 'yes' THEN TRUE WHEN 'no' THEN FALSE END AS agreed
FROM (SELECT 'Yes' AS answer UNION ALL SELECT 'no') t;

Result:

(2 rows)

Into STRING and SYMBOL

Syntax:

CAST(value AS STRING | VARCHAR | TEXT | SYMBOL)

Text. Every value can become text.

• Returns: STRING (or SYMBOL).
• NULL: stays NULL. An empty text '' is not NULL.
• Notes:
  • For a time in another format or in Nepal time use to_char.
  • To get the text inside a JSON string without quotes, use ->> or json_value_text (JSON functions).
  • || already turns numbers into text: 'CITY-' || 1.

Example

SELECT CAST(42 AS STRING) AS a, CAST(512.5 AS TEXT) AS b, CAST(CAST('1.50' AS DECIMAL(2)) AS VARCHAR) AS c,
       CAST(TRUE AS STRING) AS d, CAST(CAST('2026-09-10T11:00:00+05:45' AS TIMESTAMP) AS STRING) AS e,
       CAST(0.1 + 0.2 AS STRING) AS f;

Result:

(1 row)

Example: A label for a chart: symbol, date and close (sample data: sample)

SELECT symbol || ' ' || CAST(CAST(time AS DATE) AS STRING) || ': ' || CAST(close AS STRING) AS label
FROM ochl_day
WHERE symbol = 'VOLT'
ORDER BY time DESC
LIMIT 3;

Result:

(3 rows)

Example: JSON and BYTES as text

SELECT CAST(CAST('{"a": 1}' AS JSON) AS STRING) AS json_object,
       CAST(CAST('"text"' AS JSON) AS STRING) AS json_string,
       CAST(CAST('NA' AS BYTES) AS STRING) AS bytes;

Result:

(1 row)

Into JSON

Syntax:

CAST(value AS JSON | JSONB)

A JSON document (a text format for data, like {"name": "Sita", "tags": [1, 2]}). KDB checks it and stores it without extra spaces.

• Returns: JSON.
• NULL: SQL NULL stays NULL. The text 'null' becomes the JSON value null, which is not SQL NULL (IS NULL is false).
• Notes:
  • A text value must be valid JSON. The word CITY is not JSON; '"CITY"' (with double quotes inside) is. See JSON functions for reading parts of a document.
  • The result tables on this page show a JSON string without its quotes, and a JSON null as NULL.

Example

SELECT CAST('{"symbol": "CITY", "levels": [500, 520]}' AS JSON) AS a,
       CAST(5 AS JSON) AS b, CAST(TRUE AS JSONB) AS c,
       CAST(CAST('2026-09-10' AS DATE) AS JSON) AS d,
       CAST('null' AS JSON) AS json_null, CAST('null' AS JSON) IS NULL AS is_sql_null;

Result:

(1 row)

Example: App settings saved as JSON text

Setup:

CREATE TABLE user_settings (user_id INT PRIMARY KEY, settings STRING);
INSERT INTO user_settings VALUES (1, '{ "theme": "dark",  "alerts": ["CITY", "COAST"] }'), (2, '{"theme": "light", "alerts": []}');

SELECT user_id, CAST(settings AS JSON) AS settings_json
FROM user_settings
ORDER BY user_id;

Result:

(2 rows)

Example: Keys need double quotes (shows an error)

SELECT CAST('{theme: "dark"}' AS JSON);

Result:

Error: line 1, column 8: cannot convert '{theme: "dark"}' to JSON: not valid JSON: expected a key in double quotes at character 2 (near 'theme: "dark...')

Into BYTES

Syntax:

CAST(value AS BYTES | BYTEA | BLOB)

Raw bytes, shown as hex with \x in front (\x0a0b).

• Returns: BYTES.
• NULL: stays NULL.
• Notes: CAST(bytes AS STRING) does not give the letters back; it gives the hex text. See bytes functions for hex, unhex and base64.

Example

SELECT CAST('\x4e41' AS BYTES) AS from_hex, CAST('CITY' AS BYTEA) AS from_text,
       CAST(X'0A0B' AS BLOB) AS literal, CAST('नेपाल' AS BYTES) AS nepali, length(CAST('नेपाल' AS BYTES)) AS byte_count;

Result:

(1 row)

Example: Bad hex digits (shows an error)

SELECT CAST('\xZZ' AS BYTES);

Result:

Error: line 1, column 8: cannot convert '\xZZ' to BYTES: '\xZZ' is not hex bytes; write them like '\x0a0b' or X'0A0B'

Example: A number is not bytes (shows an error)

SELECT CAST(5 AS BYTES);

Result:

Error: line 1, column 8: cannot convert '5' to BYTES: expected bytes like X'0A0B' or '\x0a0b', got LONG 5