Default Date Format

The DEFAULT_DATE_FORMAT Rules Engine parameter specifies the default IO format to be used when converting a date into a string. The default value is DD-MON-YY.

This Rules Engine parameter applies to two different situations:

·	When the SQL functions TO_CHAR and TO_DATE are used without an IO format string. This only happens if the statement is evaluated locally, and only applies to Oracle.

For example, if the DEFAULT_DATE_FORMAT parameter is: DD MM YYYY, the SQL statement:

SELECT TO_CHAR(TRANSACTION_DATE())

results in: 15 11 2000.

The SQL statement:

SELECT TO_DATE('15-NOV-00')

results in an error message, because the date string does not correspond with the default date format.

The SQL statement

SELECT TO_DATE('15 11 2000')

results in 2000/111500000.

·	When an implicit conversion takes place from a date to a string or vice versa. This only happens if the statement is evaluated locally, and applies to several databases. However, most databases do not support implicit conversions, so if such a statement is executed on the RDBMS it will fail.

For example, if the DEFAULT_DATE_FORMAT parameter is: DD MM YYYY, the SQL statement:

SELECT TO_CHAR(TRANSACTION_DATE(),'DD MON YY')

results in: 15 NOV 00.

The SQL statement:

SELECT TO_CHAR(TO_DATE('15-NOV-00','DD-MON-YY'))

results in: 15 11 2000.

NOTE 1:

Selecting a date in the SQL Command dialog results in a date represented in the conceptual date format YYYY/MMDDHH24MISS. This is the USoft representation of a date. For example,

SELECT SYSDATE

results in 2000/0302133505.

NOTE 2:

The default date format setting only applies for statements that are evaluated locally. If you want to select the current date you can use:

·	SYSDATE is not recommended, because it is not defined whether it will be evaluated locally or on the RDBMS.

·	CURRENT_DATE() is always evaluated on the RDBMS.

·	TRANSACTION_DATE() is always evaluated locally. This does not mean that the whole statement will always be evaluated locally. That depends on whether the rest of the statement can be handled locally.

Tips to have a statement evaluated on the RDBMS are:

·	Use a SELECT... FROM ... statement and select data from a database table.

·	Use a function that is not implemented in the local evaluator, for example the SIN() function.

·	To set up the Default Date Format Rules Engine Parameter:

1.	Open the Definer.

2.	From the menu, select Tools, Rules Engine Parameters.

3.	Query for the Parameter Setting with the name DEFAULT_DATE_FORMAT

4.	Change the Setting to the date format you want to use.