Formatting Dates in Oracle

Metastorm

 

The information in this article applies to product: e-Work 6 and later

Issue

• The wrong date is being inserted into an Oracle database when using %FormatTime(%myDate,"DD-MMM-YYYY").

Resolution

• As stated in the e-Work Migration Guide, starting with version 6, e-Work formats all datetime values in compliance with the ISO standard of (YYYY-MM-DDTHH:MM:SS), e.g. "2005-02-18T23:43:05". Datetime values must be stored in the database in this format, although they can be displayed with another format using %FormatTime() or to\_char().

  Using %FormatTime(%datetime1,ddmmmyyyyy) in an INSERT statement will result in the wrong value being stored in the database.

  Using %FormatTime(%datetime2,yyyymmmdd) in an INSERT statement will result in the correct value being stored in the database.

  In Oracle, using SELECT to\_char(datetime1,'DD-MON-YYYY') will give a meaningless date because datetime1 was stored incorrectly (see INSERT above).

  Using SELECT to\_char(datetime2,'DD-MON-YYYY') will give the correct date with the specified format (e.g. 18-FEB-2005) because datetime2 was stored correctly (see INSERT above).

  See the attached procedure (6.5 - 7.x Designer is required to open it) for an example of this.