Omnis Technical Note TNDA0001
The DEFAULTDATE Keyword for Oracle
For Server Specific Keyword DEFAULTDATE.
By Colin Richardson and Chris Stevens
Due to its publication date, this technote contains information which may no longer be accurate or applicable on one or more of the platforms it refers to. Please refer to the index page which may contain updated information.
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>
and <parameter string>
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
{
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
of 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.