Re: Strange date problem -- please help !!

From: Arjan van Bentem <avbentem_at_DONT-YOU-DAREdds.nl>
Date: Tue, 9 Mar 1999 20:41:29 +0100
Message-ID: <36e5796f$0$13218@pascal>

Grinberg L. wrote
>What Oracle product makes this swap?
>SQL or PL/SQL engine?

Hmmmm, I cannot find the info I thought I was refering to. That is: I cannot find anything about Oracle swapping day and month to get a good conversion, although some other mapping is done indeed.

So, I guess I was *wrong* here, and cannot explain the behavior Jørgen described:

>01.03.1999 was stored in the database as 03.01.1999!
>(Or 03-JAN-99 as the default dispay shows me)
>
>Now, to the funny (or should I say: The part that drives me nuts !?!) part:
>When I give the Web-app a date with number_of_day_in_ month > 12 (Ex
>25.03.1999) Everything works fine !! I get 25-MAR-99 when i query the dB.

I suppose it is simply caused by the asp web application.

I'll check the paper docs tomorrow but doubt that I'll find something about swapping day and month.

---

From the Oracle 8 online docs:

String-to-Date Conversion Rules
The following additional formatting rules apply when converting string values to date values:

1. You can omit punctuation included in the format string from the date string if all the digits of the numerical format elements, including leading zeros, are specified. In other words, specify 02 and not 2 for two-digit format elements such as MM, DD, and YY.
2. You can omit time fields found at the end of a format string from the date string.
3. If a match fails between a date format element and the corresponding characters in the date string, Oracle attempts alternative format elements, as shown in Table 3-19.

Table 3-19 Oracle Format Matching

    Original Format Element

        (Additional Format Elements to Try in Place of the Original)

    'MM'    ('MON' and 'MONTH')
    'MON'    ('MONTH')
    'MONTH'    ('MON')
    'YY'    ('YYYY')
    'RR'    ('RRRR')

================================================

For Oracle Forms:

3.9.8 Default Built-in Date Format Masks

For compatibility with prior releases, if the application's BUILTIN_DATE_FORMAT property is not explicitly set, then Forms will use one of several different format masks, depending upon the type of the "object" being operated on. There are different format masks (or sets of format masks) for DATE2, DATE4, and DATETIME objects.

Here's a breakdown of which objects are considered to be DATE2, DATE4, and DATETIME:

• An item whose datatype is DATETIME is considered to be a DATETIME object.
• An item whose datatype is DATE, if it has a format mask, is considered to be a DATE4 object if the format mask contains yyyy, YYYY, rrrr, or RRRR, and a DATE2 object otherwise.
• An item whose datatype is DATE, if it has no format mask, is considered to be a DATE4 object if its length (Maximum Length) is 10 or more, and a DATE2 object otherwise (length is 9 or less).
• A parameter (as in :PARAMETER.myparam) whose datatype is DATE is considered to be a DATE2 object. Note that there are no DATETIME parameters, and a parameter's Maximum Length property applies only to CHAR parameters.
• The system variables CURRENT_DATETIME and EFFECTIVE_DATE are considered to be DATETIME objects internally.

The "builtin" format masks for each type of object are listed below:

DATE2:
    DD-MON-YY
    DD-MM-SYYYY HH24:MI:SS
DATE4:
    DD-MON-YYYY
    DD-MM-SYYYY HH24:MI:SS
DATETIME:

    DD-MON-YYYY HH24:MI:SS
    DD-MON-YYYY HH24:MI
    DD-MM-SYYYY HH24:MI:SS

Note that there are multiple format masks for each type of object. For date-to-string operations, only the first ("primary") format mask is used. The secondary format masks come into play on string-to-date operations: If the string cannot be successfully converted using the primary format mask, the secondary mask(s) are tried, in the order specified above.

---

Arjan. Received on Tue Mar 09 1999 - 13:41:29 CST