mirror of
https://github.com/MariaDB/server.git
synced 2026-03-18 06:18:41 +01:00
311ec6bbc0
Syntax:
TO_DATE(string_expression [DEFAULT string_expression ON CONVERSION ERROR],
format_string [,NLS_FORMAT_STRING])
The format_string has the same format elements as TO_CHAR(), except a
few elements that are not supported/usable for TO_DATE().
TO_DATE() returns a datetime or date value, depending on if the format
element FF is used.
Allowed separators, same as TO_CHAR():
space, tab and any of !#%'()*+,-./:;<=>
'&' can also be used if next character is not a character a-z or A-Z
"text' indicates a text string that is verbatim in the format. One cannot
use " as a separator.
Format elements supported by TO_DATE():
AD Anno Domini ("in the year of the Lord")
AD_DOT Anno Domini ("in the year of the Lord")
AM Meridian indicator (Before midday)
AM_DOT Meridian indicator (Before midday)
DAY Name of day
DD Day (1-31)
DDD Day of year (1-336)
DY Abbreviated name of day
FF[1-6] Fractional seconds
HH Hour (1-12)
HH12 Hour (1-12)
HH24 Hour (0-23)
MI Minutes (0-59)
MM Month (1-12)
MON Abbreviated name of month
MONTH Name of Month
PM Meridian indicator (After midday)
PM_DOT Meridian indicator (After midday)
RR 20th century dates in the 21st century. 2 digits
50-99 is assumed from 2000, 0-49 is assumed from 1900.
RRRR 20th century dates in the 21st century. 4 digits
SS Seconds
SYYYY Signed 4 digit year; MariaDB only supports positive years
Y 1 digit year
YY 2 digits year
YYY 3 digits year
YYYY 4 digits year
Note that if there is a missing part of the date, the current date is used!
For example if 'MM-DD HH-MM-SS' then the current year will be used.
(Oracle behaviour)
Not supported options:
- BC, D, DL, DS, E, EE, FM, FX, RM, SSSSS, TS, TZD, TZH, TZR, X,SY
BC is not supported by MariaDB datetime.
- Most of the other are exotic formats does not make sence in MariaDB as
we return datetime or datetime with fractions, not string.
- D (day-of-week) is not supported as it is not clear exactly how it would
map to MariaDB. This element depends on the NLS territory of the session.
- RR only works with 2 digit years (In Oracle RR can also work with 4
digit years in some context but the rules are not clear).
Extensions / differences compared to Oracle;
- MariaDB supports FF (fractional seconds). If FF[#] is used,
then TO_DATE will return a datetime with # of subseconds.
If FF is not used a datetime will be returned.
There is warning (no error) if string contains more digts than what
is specified with F(#]
- Names can be shortened to it's unique prefix. For example January and Ja
works fine.
- No error if the date string is shorter format_string and the next
not used character is not a number.. This is useful to get a date
from a mixed set of strings in date or datetime format.
Oracle gives an error if date string is too short.
- MariaDB supports short locales as language names
- NLS_DATE_FORMAT can use both " and ' for quoting.
- NLS_DATE_FORMAT must be a constant string.
- This is to ensure that the server knows which locale to use
when executing the function.
New formats handled by TO_CHAR():
FF[1-6] Fractional seconds
DDD Daynumber 1-366
IW Week 1-53 according to ISO 8601
I 1 digit year according to ISO 8601
IY 2 digit year according to ISO 8601
IYY 3 digit year according to ISO 8601
IYYY 4 digit year according to ISO 8601
SYYY 4 digit year according to ISO 8601 (Oracle can do signed)
Supported NLS_FORMAT_STRING options are:
NLS_CALENDAR=GREGORIAN
NLS_DATE_LANGUAGE=language
Support languages are:
- All MariaDB short locales, like en_AU.
- The following Oracle language names:
ALBANIAN, AMERICAN, ARABIC, BASQUE, BELARUSIAN, BRAZILIAN PORTUGUESE
BULGARIAN, CANADIAN FRENCH, CATALAN, CROATIAN, CYRILLIC SERBIAN CZECH,
DANISH, DUTCH, ENGLISH, ESTONIAN, FINNISH, FRENCH, GERMAN,
GREEK, HEBREW, HINDI, HUNGARIAN, ICELANDIC, INDONESIAN ITALIAN,
JAPANESE, KANNADA, KOREAN, LATIN AMERICAN SPANISH, LATVIAN,
LITHUANIAN, MACEDONIAN, MALAY, MEXICAN SPANISH, NORWEGIAN, POLISH,
PORTUGUESE, ROMANIAN, RUSSIAN, SIMPLIFIED CHINESE, SLOVAK, SLOVENIAN,
SPANISH, SWAHILI, SWEDISH, TAMIL, THAI, TRADITIONAL CHINESE, TURKISH,
UKRAINIAN, VIETNAMESE
Development bugs fixed:
MDEV-38403 Server crashes in Item_func_to_date::fix_length_and_dec upon
using an invalid argument
MDEV-38400 compat/oracle.func_to_date fails with PS protocol and cursor
protocol (Fixed by Serg)
MDEV-38404 TO_DATE: MTR coverage omissions, round 1
MDEV-38509 TO_DATE: AD_DOT does not appear to be supported
MDEV-38513 TO_DATE: NULL value for format string causes assertion failure
MDEV-38521 TO_DATE: Date strings with non-ASCII symbols cause warnings
and wrong results
MDEV-38578 TO_DATE: Possibly unexpected results upon wrong input
MDEV-38582 TO_DATE: NLS_DATE_LANGUAGE=JAPANESE does not parse values
which work in Oracle
MDEV-38584 TO_DATE: NLS_DATE_LANGUAGE=VIETNAMESE does not parse values
which work in Oracle
MDEV-38703 TO_DATE: Quotation for multi-word NLS_DATE_LANGUAGE leads
to syntax error in view definition
MDEV-38675 TO_DATE: MSAN/Valgrind/UBSAN errors in
extract_oracle_date_time
MDEV-38635 TO_DATE: UBSAN errors in item_timefunc.h upon comparison with
a view column
MDEV-38719 TO_DATE: Assertion `&my_charset_bin != charset()' failed in
String::append_for_single_quote_using_mb_wc
MDEV-38756 TO_DATE: MSAN/Valgrind errors in
Item_func_to_date::fix_length_and_dec upon PREPARE with
parameters
Known issues:
- Format string character matches inside quotes are done
one-letter-to-one-letter, like in LIKE predicate. That means things
like expansions and contractions do not work.
For example 'ss' does not match 'ß' in collations which treat them
as equal for the comparison operator.
Match is done taking into account case and accent sensitivity
of the subject argument collation, so for example this now works:
MariaDB [test]> SELECT TO_DATE('1920á12','YYYY"a"MM') AS c;
+---------------------+
| c |
+---------------------+
| 1920-12-17 00:00:00 |
+---------------------+
Co-author and reviewer: Alexander Barkov <bar@mariadb.com>
|
||
|---|---|---|
| .. | ||
| anonymous_derived.test | ||
| binlog_ptr_mysqlbinlog-master.opt | ||
| binlog_ptr_mysqlbinlog.test | ||
| binlog_stm_ps.test | ||
| binlog_stm_sp.test | ||
| binlog_stm_sp_package.test | ||
| column_compression.test | ||
| custom_aggregate_functions.test | ||
| empty_string_literal.test | ||
| events.test | ||
| exception.test | ||
| func_add_months.test | ||
| func_case.test | ||
| func_concat.test | ||
| func_decode.test | ||
| func_length.test | ||
| func_misc.test | ||
| func_months_between.test | ||
| func_pad.test | ||
| func_qualified.test | ||
| func_regexp_replace.test | ||
| func_replace.test | ||
| func_substr.test | ||
| func_time.test | ||
| func_to_char.test | ||
| func_to_date-ucs2.test | ||
| func_to_date.test | ||
| func_trim.test | ||
| func_trunc.test | ||
| func_trunc_ucs2.test | ||
| gis-debug.test | ||
| gis.test | ||
| information_schema_parameters.test | ||
| keywords.test | ||
| lower_case_table_names.opt | ||
| lower_case_table_names.test | ||
| minus.test | ||
| misc.test | ||
| mysqldump_restore.test | ||
| mysqldump_restore_func_qualified.test | ||
| ora_outer_join.test | ||
| ora_outer_join_err.test | ||
| parser.test | ||
| plugin.test | ||
| ps.test | ||
| rpl_mariadb_date.test | ||
| rpl_sp_package.test | ||
| rpl_sp_package_variables.test | ||
| sequence.test | ||
| sp-anchor-row-type-table.test | ||
| sp-anchor-type.test | ||
| sp-anonymous.test | ||
| sp-cache-invalidate.inc | ||
| sp-code.test | ||
| sp-cursor-decl.test | ||
| sp-cursor-pkg-01.inc | ||
| sp-cursor-pkg-02.inc | ||
| sp-cursor-pkg-03.inc | ||
| sp-cursor-rowtype.test | ||
| sp-cursor.test | ||
| sp-default-param.test | ||
| sp-expr.test | ||
| sp-goto-debug.test | ||
| sp-goto.test | ||
| sp-inout.test | ||
| sp-memory-leak.test | ||
| sp-package-code.test | ||
| sp-package-concurrent-dml-db.test | ||
| sp-package-concurrent-dml-package.test | ||
| sp-package-concurrent-dml-trigger.test | ||
| sp-package-concurrent-dml-view.test | ||
| sp-package-concurrent-dml.inc | ||
| sp-package-i_s.test | ||
| sp-package-innodb.test | ||
| sp-package-mdl.test | ||
| sp-package-mysqldump.test | ||
| sp-package-security.test | ||
| sp-package.test | ||
| sp-param.inc | ||
| sp-param.test | ||
| sp-record.test | ||
| sp-row-vs-var.inc | ||
| sp-row.test | ||
| sp-security.test | ||
| sp-sys_refcursor-alias.test | ||
| sp-sys_refcursor-func_hybrid.test | ||
| sp-sys_refcursor.test | ||
| sp.test | ||
| statement-expr.test | ||
| table_value_constr.test | ||
| trigger.test | ||
| trigger_notembedded.test | ||
| truncate.test | ||
| type_blob.test | ||
| type_clob.test | ||
| type_date.test | ||
| type_number.test | ||
| type_raw.test | ||
| type_varchar.test | ||
| type_varchar2.test | ||
| update.test | ||
| update_innodb.test | ||
| variables.test | ||
| vcol.test | ||
| vcol_innodb.test | ||
| versioning.test | ||
| win.test | ||