TO_DATE
Description
TO_DATE
converts STRING input to DATE output. You must define the FORMAT
. If the input does not match the FORMAT
, the function returns NULL.
Supported input column type: STRING.
Output column type: DATE.
Syntax
TO_DATE ( table.column, FORMAT ( format ) )
FORMAT
syntax
The <format>
defines how the input STRING should be interpreted.
The following special fields in <format>
are supported:
%Y
: 4 digit year.%m
: 1-2 digit month of year. A leading zero is permitted, but not required.%d
: 1-2 digit day of month. A leading zero is permitted, but not required.%H
: 1-2 digit hour of day in 24-hour format. A leading zero is permitted, but not required.%M
: 1-2 digit minutes per hour. A leading zero is permitted, but not required.%S
: 1-2 digit seconds per minute. A leading zero is permitted, but not required.%F
: 1-3 digit milliseconds per second. Leading zeroes are permitted, but not required.%%
: A literal "%
" character.
All other FORMAT
content must exactly match the input STRING. If the input value does not fit the given FORMAT
, the output value will be NULL.
All parts of the timestamp which are not set in the pattern will be set to their base value. The base values are:
Year: 1970
Month: 01
Day: 01
Hour: 00
Minute: 00
Second: 00
Milliseconds: 000
Null handling
If the input value is NULL, the output value is NULL as well.
Examples
[1] Example in which a STRING constant is converted to a DATE type. | ||||||||
| ||||||||
|
[2] Example in which STRING inputs with different digit widths are converted to the DATE type. | ||||||||||||||||
| ||||||||||||||||
|
[3] Example in which only hours and minutes without a date are contained in the STRING constant. The date is set to the base date 01.01.1970 in the resulting DATE value, and the seconds and milliseconds are both set to 0. Leading zeros for hour and minute values are permitted, but not required: | ||||||||||||||
| ||||||||||||||
|