Functions
DATE
DATE(<expression-1> [ , <expression-2> ] )
The DATE function converts a VARCHAR or BIGINT expression to a date.
Without the 2nd parameter, ISO dates are recognized: yyyy, yyyy-MM, or yyyy-MM-dd (month and day two digits). A missing month is January; a missing day is the 1st. An ISO timestamp is also accepted; only the date portion is used.
If the 1st expression is a BIGINT (without a 2nd parameter), it is interpreted as the number of whole days since the Unix epoch 1970-01-01. 0 is 1 January 1970; positive values are after that date, negative values before. A format as 2nd parameter is not allowed with BIGINT.
With two parameters, the 2nd VARCHAR expression specifies the format (see DateTimeFormatter). A possible time portion is ignored and only the date portion is used.
Optional format parts are written in square brackets, e.g. 'yyyy-MM-dd[ HH[:mm]]'. A space immediately before [ is moved into the optional section: 'yyyy-MM-dd [HH:mm]' is treated as 'yyyy-MM-dd[ HH:mm]' and matches a date with or without a time. Without that shift the space would be required.
If month or day are missing, only the available parts are used (missing month/day = 1).
If the value or the format is NULL, or the value is empty, the result is NULL.
The result has the data type DATE.
The following format instructions should be used:
| Letter | Meaning | Example | Result |
|---|---|---|---|
| y / u | Year | yyyy; yy | 2011; 11 |
| M | Month | MM; M; M[M] | 01; 1; 1 or 01 |
| d | Day | dd; d; d[d] | 04; 4; 4 or 04 |
| [ ] | optional section | yyyy-MM-dd[ HH:mm] | date with or without time |
For one- or two-digit month and day use M[M] and d[d]:
SELECT DATE('2026-9-1', 'yyyy-M[M]-d[d]')
, DATE('2026-09-01', 'yyyy-M[M]-d[d]');
Output
| column_1 | column_2 |
|---|---|
| 2026-09-01 | 2026-09-01 |
An example could look like this. If column d (type character string) has the value '2011-11-1', then
SELECT DATE(d, 'yyyy-M-d')
-- Result: 2011-11-01
For Excel dates the DATE function is overridden in the Excel driver, see also here.
Example
Without a format: year, year-month, or a full date.
SELECT DATE('2026')
, DATE('2026-02')
, DATE('2026-02-18');
Output
| column_1 | column_2 | column_3 |
|---|---|---|
| 2026-01-01 | 2026-02-01 | 2026-02-18 |
SELECT DATE('2024-03-27')
, DATE('27.5.2024', 'dd.MM.yyyy')
, DATE('20211224', 'yyyyMMdd')
, DATE('24.12.2021 18:30:10', 'dd.MM.yyyy[ HH:mm:ss]')
, DATE('2024-03', 'yyyy-MM');
Output
| column_1 | column_2 | column_3 | column_4 | column_5 |
|---|---|---|---|---|
| 2024-03-27 | 2024-05-27 | 2021-12-24 | 2021-12-24 | 2024-03-01 |
Optional format parts and missing fields in the value:
SELECT DATE('2024-03-27', 'yyyy-MM-dd [HH:mm]')
, DATE('2024-03-27 14:30', 'yyyy-MM-dd [HH:mm]')
, DATE('2024', 'yyyy');
Output
| column_1 | column_2 | column_3 |
|---|---|---|
| 2024-03-27 | 2024-03-27 | 2024-01-01 |
Unix epoch: the BIGINT value is the number of days since 1970-01-01.
SELECT DATE(0)
, DATE(1)
, DATE(-1);
Output
| column_1 | column_2 | column_3 |
|---|---|---|
| 1970-01-01 | 1970-01-02 | 1969-12-31 |
TIMESTAMP
TIMESTAMP(<expression-1> [ , <expression-2> ] )
The TIMESTAMP function converts a VARCHAR or BIGINT expression to a timestamp (date and time).
The time zone in the value is optional. Without a zone, local time applies; with Z (offset or zone name), the value is converted to local time.
If the 1st expression is a BIGINT (without a 2nd parameter), it is interpreted as the number of seconds since the Unix epoch 1970-01-01 00:00:00 UTC and converted to local time. A format as 2nd parameter is not allowed with BIGINT.
If the value or the format is NULL, or the value is empty, the result is NULL.
Without the 2nd parameter, common ISO-like formats are recognized, e.g.:
2026,2026-02,2026-02-18(missing month = January, missing day = 1, time midnight)2024-03-27 14:30:002024-03-27T14:30:00- optional fractional seconds
- optional
Z, offset (+02:00), or zone name (GMT)
Without a zone the clock time is kept; Z is UTC:
SELECT TIMESTAMP('2026-09-01 00:00')
, TIMESTAMP('2026-09-01 00:00Z');
In the Europe/Berlin time zone (September = CEST, UTC+2) this yields:
| column_1 | column_2 |
|---|---|
| 2026-09-01 00:00:00.0 | 2026-09-01 02:00:00.0 |
With two parameters, the 2nd VARCHAR expression specifies the format (see DateTimeFormatter). Optional parts are written in [], e.g. 'yyyy-MM-dd[ HH:mm[:ss]][X]' or 'yyyy-M-d HH:mm:ss[ z]'. The zone in the value is then optional: '2026-09-01 00:00' and '2026-09-01 00:00Z' both match the same pattern. If time fields are missing, midnight or 0 for minute/second is used.
Common time and zone letters:
| Letter | Meaning | Example |
|---|---|---|
| H | Hour 0–23 | HH; H |
| m | Minute | mm; m |
| s | Second | ss; s |
| z | Zone name | GMT; CET |
| X / XXX | Offset | Z; +02:00 |
The result has the data type TIMESTAMP.
Example
Without a second parameter (ISO-like) and with a fixed pattern:
SELECT TIMESTAMP('2024-03-27 14:30:00')
, TIMESTAMP('2024-03-27T15:45:30')
, TIMESTAMP('2024-03-27')
, TIMESTAMP('27.03.2024 16:00', 'dd.MM.yyyy HH:mm');
Output
| column_1 | column_2 | column_3 | column_4 |
|---|---|---|---|
| 2024-03-27 14:30:00.0 | 2024-03-27 15:45:30.0 | 2024-03-27 00:00:00.0 | 2024-03-27 16:00:00.0 |
Optional format parts: the same pattern recognizes a date alone, with hour/minute, or with seconds.
SELECT TIMESTAMP('2024-03-27', 'yyyy-MM-dd[ HH:mm[:ss]]')
, TIMESTAMP('2024-03-27 14:30', 'yyyy-MM-dd[ HH:mm[:ss]]')
, TIMESTAMP('2024-03-27 14:30:45', 'yyyy-MM-dd[ HH:mm[:ss]]');
Output
| column_1 | column_2 | column_3 |
|---|---|---|
| 2024-03-27 00:00:00.0 | 2024-03-27 14:30:00.0 | 2024-03-27 14:30:45.0 |
Missing fields in the value: only the available parts are used (day = 1, minute/second = 0).
SELECT TIMESTAMP('2024-03', 'yyyy-MM')
, TIMESTAMP('2024-03-27 14', 'yyyy-MM-dd HH');
Output
| column_1 | column_2 |
|---|---|
| 2024-03-01 00:00:00.0 | 2024-03-27 14:00:00.0 |
Optional time zone in the pattern: if a zone or offset is given, it is used and converted to local time. If it is missing, the local time is kept.
SELECT TIMESTAMP('2026-10-01 15:30:00', 'yyyy-M-d HH:mm:ss[ z]')
, TIMESTAMP('2026-10-01 15:30:00 GMT', 'yyyy-M-d HH:mm:ss[ z]')
, TIMESTAMP('2024-03-27T15:45:30Z')
, TIMESTAMP('2024-03-27T15:45:30+02:00');
In the Europe/Berlin time zone (October = CEST, UTC+2; March = CET, UTC+1) this yields:
| column_1 | column_2 | column_3 | column_4 |
|---|---|---|---|
| 2026-10-01 15:30:00.0 | 2026-10-01 17:30:00.0 | 2024-03-27 16:45:30.0 | 2024-03-27 14:45:30.0 |
15:30 GMT and 15:30+00:00 are the same instant:
SELECT TIMESTAMP('2026-10-01 15:30:00 GMT', 'yyyy-M-d HH:mm:ss[ z]')
= TIMESTAMP('2026-10-01 15:30:00+00:00', 'yyyy-M-d HH:mm:ss[XXX]');
Output: true
Unix epoch: the BIGINT value is the number of seconds since 1970-01-01 00:00:00 UTC.
SELECT TIMESTAMP(0) = TIMESTAMP('1970-01-01T00:00:00Z')
, TIMESTAMP(1) = TIMESTAMP('1970-01-01T00:00:01Z');
Output: true, true
LTRIM
LTRIM(<expression-1> [ , <expression-2> ] )
Deletion of leading characters in the 1st VARCHAR expression.
The 2nd VARCHAR expression is optional and specifies a set of characters to be deleted. If not specified, the set is limited to spaces.
Deletes at the beginning of the first expression all characters that are in this set, up to the first character outside the set.
The result has the data type VARCHAR.
Example
SELECT LTRIM(' stays'), LTRIM('!"$% &%$stays', ' !"$%&');
Output
column_1 column_2
-------- --------
stays stays
RTRIM
RTRIM(<expression-1> [ , <expression-2> ] )
Deletion of trailing characters in the 1st VARCHAR expression.
The 2nd VARCHAR expression is optional and specifies a set of characters to be deleted. If not specified, the set is limited to spaces.
Deletes at the end of the first expression all characters that are in this set, up to the first character outside the set.
The result has the data type VARCHAR.
Example
SELECT RTRIM('stays '), RTRIM('stays!"$% &%$', ' !"$%&');
Output
column_1 column_2
-------- --------
stays stays
TRIM
TRIM(<expression>)
Deletion of leading and trailing "whitespace" characters in the VARCHAR expression.
The function uses the java trim function (see Java 8 trim).
All characters in the ASCII code less than or equal to space are considered whitespace.
The result has the data type VARCHAR.
Example
SELECT TRIM(' stays '), TRIM(CHR(9) || ' stays ' || CHR(13) || CHR(10));
Output
column_1 column_2
-------- --------
stays stays
LOWER
LOWER(<expression>)
Convert all characters in the VARCHAR expression to lowercase.
The result has the data type VARCHAR.
Example
SELECT LOWER('Aa123'), lower('MiXeD');
Output
column_1 column_2
-------- --------
aa123 mixed
UPPER
UPPER(<expression>)
Convert all characters in the VARCHAR expression to uppercase.
The result has the data type VARCHAR.
Example
SELECT Upper('Aa123'), UPPER('MiXeD');
Output
column_1 column_2
-------- --------
AA123 MIXED
BIGINT
BIGINT(<expression>)
Convert the VARCHAR expression to a numeric, integer value.
Uses java Long.valueOf() and throws a runtime error if the string does not match the number format.
The result has the data type BIGINT.
Example
SELECT 1*'123';
Output
Implicit Conversion not yet implemented
SELECT 1*BIGINT('123');
Output
column_1
--------
123
SELECT 1*BIGINT('A123');
Output
java.lang.NumberFormatException: For input string: "A123"
DOUBLE
DOUBLE(<expression>)
Convert the VARCHAR expression to a numeric decimal value.
Uses java Double.valueOf() and throws a runtime error if the string does not match the number format.
The result has the data type DOUBLE.
Example
SELECT 9*'3.14159';
Output
Implicit Conversion not yet implemented
SELECT 9*'DOUBLE(3.14159)';
Output
column_1
--------
28.27431
SELECT 1*DOUBLE('A123');
Output
java.lang.NumberFormatException: For input string: "A123"
ASCII
ASCII(<expression>)
Convert the first character in the VARCHAR expression to the ASCII value.
The result has the data type BIGINT.
Example
SELECT ASCII('A'), ascii('ABC');
Output
column_1 column_2
-------- --------
65 65
LENGTH
LENGTH(<expression>)
Determine the length of a VARCHAR expression.
The result has the data type BIGINT.
Example
SELECT LENGTH('A'), lengTH('ABC');
Output
column_1 column_2
-------- --------
1 3
CHR
CHR(<expression>)
Convert the numeric expression to the corresponding ASCII character
The result has the data type VARCHAR.
Example
SELECT CHR(65), chr(35);
Output
column_1 column_2
-------- --------
A #
LPAD
LPAD(<expression-1>, <expression-2> [ , <expression-3> ])
Pad the 1st VARCHAR expression on the left with the 3rd VARCHAR expression until the length of the 2nd BIGINT expression is reached.
The 3rd VARCHAR expression is optional. If not specified, padding is with spaces.
The result has the data type VARCHAR.
Example
SELECT LPAD('x', 10), LPAD('x', 10, '1'), LPAD('xy', 10, 'abc'), lPad('xxxxxxxxxx', 10, '1');
Output
column_1 column_2 column_3 column_4
-------- -------- -------- --------
x 111111111x abcabcabxy xxxxxxxxxx
In the 3rd example the 3rd expression is first inserted as often as it fits completely. Then the rightmost characters that no longer fit as padding are truncated
RPAD
RPAD(<expression-1>, <expression-2> [ , <expression-3> ])
Pad the 1st VARCHAR expression on the right with the 3rd VARCHAR expression until the length of the 2nd BIGINT expression is reached.
The 3rd VARCHAR expression is optional. If not specified, padding is with spaces.
The result has the data type VARCHAR.
Example
SELECT RPAD('x', 10), RPAD('x', 10, '1'), RPAD('xy', 10, 'abc'), rPad('xxxxxxxxxx', 10, '1');
Output
column_1 column_2 column_3 column_4
-------- -------- -------- --------
x x111111111 xyabcabcab xxxxxxxxxx
In the 3rd example the 3rd expression is first inserted as often as it fits completely. Then the rightmost characters that no longer fit as padding are truncated
SUBSTR
SUBSTR(<expression-1>, <expression-2> [ , <expression-3> ])
Output of parts of the 1st VARCHAR expression.
The 2nd BIGINT expression gives the start position (starting with 1) and the optional 3rd BIGINT expression the length. The 3rd BIGINT expression is optional. If not specified, the remainder from the start position is taken. If it is longer than the remaining length, the remainder from the start position is also returned.
The result has the data type VARCHAR.
Example
SELECT SUBSTR('12345678', 4), substr('12345678', 2, 4), SUBSTR('12345678', 8, 4);
Output
column_1 column_2 column_3
-------- -------- --------
45678 2345 8
SELECT SUBSTR('12345678', 9);
Output
[position=1:27] Index out of bounds
INSTR
INSTR(<expression-1>, <expression-2> [ , <expression-3> [ , <expression-4> ] ])
Search for the 2nd VARCHAR expression in the 1st VARCHAR expression.
If the 3rd BIGINT expression is specified, the search starts from the corresponding position. The additional 4th BIGINT expression specifies which occurrence of the 2nd VARCHAR expression is to be searched for.
If the search is not successful, the value 0 is returned.
The result has the data type Bigint.
Example
SELECT instr('12341234', '1'), INSTR('12341234', '21'), INSTR('12341234', '1', 6), INSTR('12341234', '1', 1, 2);
Output
column_1 column_2 column_3 column_4
-------- -------- -------- --------
1 0 0 5
REPLACE
REPLACE(<expression-1>, <expression-2>, <expression-3>)
In the 1st VARCHAR expression replace all occurrences of the 2nd VARCHAR expression with the 3rd VARCHAR expression.
The function uses the java replace function (see Java 8 replace).
The result has the data type VARCHAR.
Example
SELECT REPLACE('AAAdas bleibtAAAAA', 'A', 'B'), REPLACE('AAAdas bleibtAAAAA', 'B', ''), REPLACE('AAA', 'A', '');
Output
column_1 column_2 column_3
-------- -------- --------
BBBdas bleibtBBBBB AAAdas bleibtAAAAA
REGEXP_REPLACE
REGEXP_REPLACE(<expression-1>, <expression-2>, <expression-3>)
In the 1st VARCHAR expression, all matches of the regular expression given in the 2nd VARCHAR expression are replaced with the 3rd VARCHAR expression.
The function uses the java replaceAll function (see Java 8 replaceAll).
The result has the data type VARCHAR.
Example
SELECT REGEXP_REPLACE('AAAdas bleibtBUHZ', '[A-Z]', 'B'), REGEXP_REPLACE('AAAdas bleibtAAAAA', 'B', ''), REGEXP_REPLACE('AAA', '.', '');
Output
column_1 column_2 column_3
-------- -------- --------
BBBdas bleibtBBBB AAAdas bleibtAAAAA