Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-10764 PL/SQL parser - Phase 2
  3. MDEV-10575

sql_mode=ORACLE: Date and timestamp formats

    XMLWordPrintable

    Details

    • Type: Technical task
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: OTHER
    • Labels:

      Description

      When running with sql_mode=ORACLE, MariaDB should emulate Oracle-style behavior related to date and timestamp formats.

      Oracle uses '17-AUG-16' and '17-AUG-16 06.25.36.000000 PM' as the default format for the DATE and TIMESTAMP data types:

      DROP TABLE t1;
      CREATE TABLE t1 (d DATE, ts TIMESTAMP);
      INSERT INTO t1 VALUES (CURRENT_DATE, CURRENT_TIMESTAMP);
      SELECT * FROM t1;
      

      D
      ------------------
      TS
      ---------------------------------------------------------------------------
      17-AUG-16
      17-AUG-16 06.27.16.000000 PM
      

      Formats are determined by session parameters:

      SELECT * FROM nls_session_parameters
      WHERE  parameter LIKE '%FORMAT%';
      

      NLS_DATE_FORMAT          DD-MON-RR
      NLS_TIME_FORMAT          HH.MI.SSXFF AM
      NLS_TIMESTAMP_FORMAT     DD-MON-RR HH.MI.SSXFF AM
      NLS_TIME_TZ_FORMAT       HH.MI.SSXFF AM TZR
      NLS_TIMESTAMP_TZ_FORMAT  DD-MON-RR HH.MI.SSXFF AM TZR
      

      The above parameters detemine both input and output formats and can be changed using an ALTER SESSION query.

      ALTER SESSION SET nls_date_format = 'yyyy-mm-dd';
      DROP TABLE t1;
      CREATE TABLE t1 (d DATE);
      INSERT INTO t1 VALUES ('2001-10-20');
      INSERT INTO t1 VALUES (CURRENT_DATE);
      SELECT * FROM t1;
      

      D
      ----------
      2001-10-20
      2016-08-17
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              bar Alexander Barkov
              Reporter:
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

                Dates

                Created:
                Updated: