PL/SQL parser - Phase 2 (MDEV-10764)

[MDEV-10575] sql_mode=ORACLE: Date and timestamp formats Created: 2016-08-17  Updated: 2021-05-11

Status: Open
Project: MariaDB Server
Component/s: OTHER
Affects Version/s: None
Fix Version/s: None

Type: Technical task Priority: Major
Reporter: Alexander Barkov Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: Compatibility

Issue Links:
Blocks
is blocked by MDEV-4912 Data type plugin API version 1 Closed
Relates
relates to MDEV-11829 Please add support for datetime with ... Open

 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



 Comments   
Comment by Elena Stepanova [ 2017-04-23 ]

Should probably be 10.3?

Generated at Thu Feb 08 07:43:16 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.