TO_DATE / TO_TIMESTAMP
DATE data type is not documented, although it works.
Wenn DATE nicht relevant Seite bereinigen.
Unclear
Both TO_DATE and TO_TIMESTAMP exist? Because:
sql
SELECT
TO_CHAR(TO_DATE('11-NOV-2021')) as expr,
LENGTH(TO_DATE('11-NOV-2021')) as results
UNION ALL
SELECT
TO_CHAR(TO_DATE('11-11-2021 11:12:13','DD-MM-YYYY HH:MI:SS')) as expr,
LENGTH(TO_DATE('11-11-2021 11:12:13','DD-MM-YYYY HH:MI:SS')) as results
UNION ALL
SELECT
TO_CHAR(TO_TIMESTAMP('11-11-2021 11:12:13','DD-MM-YYYY HH:MI:SS')) as expr,
LENGTH(TO_TIMESTAMP('11-11-2021 11:12:13','DD-MM-YYYY HH:MI:SS')) as results;txt
+---------------------------------+---------+
| EXPR | RESULTS |
+---------------------------------+---------+
| 11-NOV-21 | 9 |
| 11-NOV-21 | 9 |
| 11-NOV-21 11.12.13.000000000 AM | 31 |
+---------------------------------+---------+Unclear
During conversion, one hour is subtracted during winter time and two hours during summer time: Is it UTC? See example below.
If UTC: Note must be included in this documentation.
The functions convert expr into a value of data type DATE or TIMESTAMP.
Syntax
sql
TO_DATE(expr[,fmt])
or
TO_TIMESTAMP(expr[,fmt])Arguments
expr: a string of data type STRING.
fmt (optional): a date/time model format that specifies the format of expr.
The table has not been fully checked: Only transferred from the source.
The following has been checked and does not work:
Does not work in MKW
| J | Julian day; the number of days since January 1, 4712 BC. Numbers specified with J must be integers. ::: |
| Element | Description |
| - / , . ; : "text" | Punctuation and quoted text are reproduced in the result. |
| AD A.D. | AD indicator with or without periods. |
| AM A.M. | Meridian indicator with or without periods. |
| BC B.C. | BC indicator with or without periods. |
| CC* SCC* | Century. If the last 2 digits of a 4-digit year are between 01 and 99 (inclusive), then the century is one greater than the first 2 digits of that year. If the last 2 digits of a 4-digit year are 00, then the century is the same as the first 2 digits of that year. If the last 2 digits of a 4-digit year are between 01 and 99 (inclusive), then the century is one greater than the first 2 digits of that year. If the last 2 digits of a 4-digit year are 00, then the century is the same as the first 2 digits of that year. Example: 2002 returns 21; 2000 returns 20. |
| D | Day of the week (1-7). This element depends on the NLS territory of the session. |
| DAY | Name of the day. |
| DD | Day of the month (1-31). |
| DDD | Day of the year (1-366). |
| DL | Returns a value in long date format, which is an extension of the DATE format. Restriction: You can only specify this format with the TS element, separated by spaces. |
| DS | Returns a value in short date format. Restriction: You can only specify this format with the TS element, separated by spaces. |
| DY | Abbreviated day name. |
| E | Abbreviated era name (Japanese imperial calendar, official ROC calendar, and Thai Buddha calendar). |
| EE | Full era name (Japanese imperial calendar, official ROC calendar, and Thai Buddha calendar). |
| FF [1..9] | Fractions of a second; no radix character is printed. Use the format element X to add the radix character. Use the numbers 1 to 9 after FF to specify the number of digits in the fractional second of the returned date/time value. If you do not specify a digit, Database uses the precision specified for the date/time data type or the default precision of the data type. Valid in timestamp and interval formats, but not in DATE formats. For example: 'HH:MI:SS.FF' SELECT TO_CHAR(SYSTIMESTAMP, 'HH:MI:SS.FF'); |
| FM | Returns a value without leading or trailing spaces. See also: FM |
| FX | Requires exact match between character data and format model. See also: FX |
| HH HH12 | Hour of the day (1-12). |
| HH24 | Hour of the day (0-23). |
| IW* | Calendar week of the year (1-52 or 1-53), as defined in the ISO-8601 standard. A calendar week begins on Monday. The first calendar week of the year includes January 4. The first calendar week of the year can include December 29, 30, and 31. The last calendar week of the year can include January 1, 2, and 3. |
| IYYY* | 4-digit year that includes the calendar week, as defined in the ISO-8601 standard. |
| IYY* IY* I* | Last 3, 2, or 1 digit(s) of the year that includes the calendar week, as defined in the ISO-8601 standard. |
| MI | Minute (0-59). |
| MM | Month (01-12; January = 01). |
| MON | Abbreviated month name. |
| MONTH | Month name. |
| PM P.M. | Meridian indicator with or without periods. |
| Q* | Quarter of the year (1, 2, 3, 4; January - March = 1). |
| RM | Month in Roman numerals (I-XII; January = I). |
| RR | Allows storage of 20th century dates in the 21st century using only two digits. See also: The RR date/time format element |
| RRRR | Round year. Accepts either 4- or 2-digit input. For 2-digit input, returns the same result as RR. If you do not want this feature, enter the 4-digit year. |
| SS | Second (0-59). |
| SSSSS | Seconds past midnight (0-86399). |
| TS | Returns a value in short time format. Restriction: You can only specify this format with the DL or DS element, separated by spaces. |
| TZD | Daylight saving time information. The TZD value is an abbreviated time zone string with daylight saving time information. It must correspond to the region specified in TZR. Valid in timestamp and interval formats, but not in DATE formats. Example: PST (for US/Pacific Standard Time); PDT (for US/Pacific Daylight Time). |
| TZH | Time zone hour. (See TZM format element.) Valid in timestamp and interval formats, but not in DATE formats. Example: ,HH:MI:SS.FFTZH:TZM‘. |
| TZM | Time zone minute. (See TZH format element.) Valid in timestamp and interval formats, but not in DATE formats. Example: ,HH:MI:SS.FFTZH:TZM‘. |
| TZR | Time zone region information. The value must be one. |
| WW* | Week of the year (1-53), where week 1 begins on the first day of the year and ends on the seventh day of the year. |
| W* | Week of the month (1-5), where week 1 begins on the first day of the month and ends on the seventh. |
| X | Local radix character. Example: „HH:MI:SSXFF“. |
| Y,YYY | Year with comma at this position. |
| YEAR* SYEAR* | Year spelled out; S prefixes BC dates with a minus sign (-). |
| YYYY SYYYY | 4-digit year; S prefixes BC dates with a minus sign. |
| YYY YY Y | Last 3, 2, or 1 digit(s) of the year. |
* Not supported by TO_DATE
Return Value
The return value is DATE or TIMESTAMP.
Usage Notes
- If fmt is omitted, expr must be in the default date format: 'DD-MON-YYYY'.
- Do not use the
TO_DATEandTO_TIMESTAMPfunctions with aDATEvalue (orTIMESTAMPvalue) for the expr argument. The first two digits of the returnedDATEvalue (orTIMESTAMPvalue) may differ from the original expr depending on fmt or the default date format.
Does not work in MKW
- If fmt =
J(Julian calendar), expr must be an integer.
Examples
sql
SELECT
'to_date(`11-11-2021`,`DD-MM-YYYY`)' as expr,
to_date('11-11-2021','DD-MM-YYYY') as results
UNION ALL
SELECT
'to_date(`11-NOV-2021`,`DD-MON-YYYY`)' as expr,
to_date('11-NOV-2021','DD-MON-YYYY') as results
UNION ALL
SELECT
'to_date(`11-11-2021 11:12:13`,`DD-MM-YYYY HH:MI:SS`)' as expr,
to_date('11-11-2021 11:12:13','DD-MM-YYYY HH:MI:SS') as results
UNION ALL
SELECT
'to_date(`11-11-2021 11:12:13`,`DD-MM-YYYY MI:SS:HH24`)' as expr,
to_date('11-11-2021 11:12:13','DD-MM-YYYY MI:SS:HH24') as results
UNION ALL
SELECT
'to_date(`11-NOV-2021`)' as expr,
to_date('11-NOV-2021') as results
;+--------------------------------------------------------+--------------------------+
| EXPR | RESULTS |
+--------------------------------------------------------+--------------------------+
| TO_DATE(`11-11-2021`,`DD-MM-YYYY`) | 2021-11-10T23:00:00.000Z |
| TO_DATE(`11-NOV-2021`,`DD-MON-YYYY`) | 2021-11-10T23:00:00.000Z |
| TO_DATE(`11-11-2021 11:12:13`,`DD-MM-YYYY HH:MI:SS`) | 2021-11-11T10:12:13.000Z |
| TO_DATE(`11-11-2021 11:12:13`,`DD-MM-YYYY MI:SS:HH24`) | 2021-11-11T12:11:12.000Z |
| TO_DATE(`11-NOV-2021`) | 2021-11-10T23:00:00.000Z |
+--------------------------------------------------------+--------------------------+