Omnis Technical Note TNDA0001

The DEFAULTDATE Keyword for Oracle

For Server Specific Keyword DEFAULTDATE.
By Colin Richardson and Chris Stevens

Use the DEFAULTDATE keyword to change the default settings for each part of a date field sent to
Oracle. These defaults are then sent to the server when the Omnis datetime subtype specifies only
a portion of a complete datetime, e.g Short time, Datetime DMY. The default settings affect all date processing within the context of the current session.

The syntax of the keyword is:

< DEFAULTDATE> < parameter list>
where <parameter list>=
<parameter string> [ { , <parameter string> }.... ]
and <parameter string> is one of the following:

Cxx or cxx for a default century, where xx is between 0 and 99
Yxx or yxx for a default year, where xx is between 0 and 99
Mxx or mxx for a default month, where xx is between 0 and 12
Dxx or dxx for a default day, where xx is between 0 and 31
Hxx or hxx for a default hour, where xx is between 0 and 23
MIxx or Mixx for a default minute, where xx is between 0 and 59
Sxx or sxx for a default minute, where xx is between 0 and 59.

f the xx value is omitted then the default of 0 is set.

If this functionality is not invoked then for any un-set part of a date sent to Oracle the default value of
0 is passed. This is the default used by previous versions of the DAM.

Note: Using a setting of C0,Y0 is not equivalent to a default setting of C,Y as the previous setting sends the values for both the century and year in a valid format which Oracle interprets as a century and year
of 0000. The latter is accepted for backwards compatibility but should be avoided as both values will be passed unformatted to Oracle and can cause problems when displaying dates. For this reason the user should always set a valid default, e.g. C19,Y00 before inserting new data into a server.

For example, if the application issues the following:

Local variable DATE1 (Short time)
Server specific keyword
{< DEFAULTDATE> C19,Y00,M05,mi10,d8,h12,s1}

Calculate DATE1 as '15:26'
Perform SQL {Insert into test_date values(@[DATE1])}
Perform SQL {select (to_char(col1,
'MON-DD-YYYY HH:MI:SS')) from test_date}
; where test_date is an Oracle table
; with a single column col1 of type DATE

When the results of the select are returned into a character field using the format string, this
field will contain 'MAY-08-1900 03:26:01' .This is due to the default date/time setting
8 MAY 1900 12:10:01. The example uses a short time field that only provides the hour
and minute, all other parts of the date are passed using the default values.

Note: that empty or NULL dates will be stored as NULL dates on the server and will not use the default settings.