Specifies whether columns with the DATE data type or with the
built-in function CURRENT_TIMESTAMP are interpreted as VMS or
SQL99 format.
1 – Environment
You can use the SET DEFAULT DATE FORMAT statement:
o In interactive SQL
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
2 – Format
(B)0[m[1;4mSET[m[1m [1;4mDEFAULT[m[1m [1;4mDATE[m[1m [1;4mFORMAT[m[1m qq> runtime-options qqqq> [m [1m [m (B)0[m[1mruntime-options [m [1m [m [1mqqwqqq> 'string-literal' qqqqqqwqqqqq> [m [1m tqqq> parameter qqqqqqqqqqqqqu [m [1m mqqq> parameter-marker qqqqqqj [m
3 – Arguments
3.1 – parameter
Specifies the value of runtime-options, which must be one of the
following:
o SQL99
o SQL92
o VMS
3.2 – parameter-marker
Specifies the value of runtime-options, which must be one of the
following:
o SQL99
o SQL92
o VMS
3.3 – SQL99 or SQL92
Specifies that the DATE data type and the CURRENT_TIMESTAMP
built-in function are interpreted as SQL standard. The SQL
standard format DATE contains only the YEAR TO DAY fields, and
CURRENT_TIMESTAMP returns a TIMESTAMP data type.
3.4 – 'string-literal'
Specifies the value of runtime-options, which must be one of the
following:
o SQL99
o SQL92
o VMS
3.5 – VMS
Specifies that the DATE data type and the CURRENT_TIMESTAMP
built-in function are interpreted as VMS format. The VMS format
DATE and CURRENT_TIMESTAMP contain YEAR TO SECOND fields.
4 – Example
Example 1: Changing the DATE format to SQL99
In the following example, SQL issues an error because, by
default, the DATE data type is in OpenVMS DATE format. That is,
it contains the fields YEAR through SECOND. The SET DEFAULT DATE
FORMAT statement changes the default to ANSI/ISO format so that
the CURRENT_DATE and DATE types are compatible.
SQL> set default date format 'VMS';
SQL> --
SQL> create domain LOGGING_DATE
cont> DATE
cont> default CURRENT_DATE;
%SQL-F-DEFVALINC, You specified a default value for LOGGING_DATE
which is inconsistent with its data type
SQL> --
SQL> set default date format 'SQL99';
SQL> --
SQL> create domain LOGGING_DATE
cont> DATE
cont> default CURRENT_DATE;
SQL> show domain LOGGING_DATE;
LOGGING_DATE DATE ANSI
Oracle Rdb default: CURRENT_DATE