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: