This discussion is archived
6 Replies Latest reply: Jan 25, 2013 12:06 PM by matthew_morris RSS

issue with date picker date format on page load

skapex Newbie
Currently Being Moderated
Hi,

Apex 4.1

I came across an issue with date picker format mask.

I have a date picker (P1_START_DATE) on my page with format mask 'DD-MON-YYYY'. I have an on load before header process which loads into this item.

SELECT TO_DATE(SYSDATE, 'DD-MON-YYYY') INTO :P1_START_DATE FROM DUAL;

I have a report which uses this date picker for filtering and submit button on click of which report data is filtered for different dates.

report where clause is like-

WHERE ts_date=TO_DATE(:P1_START_DATE,'DD-MON-YYYY')

The issue is on first time page load when I check in session date format is like '24-JAN-13',due to which my report takes date as '24-JAN-0013' and shows wrong data.

When I submit page by clicking on submit button session value shows '24-JAN-2013' and report data shown correctly.

I could solve this by changing date format in my where clause as below

WHERE ts_date=TO_DATE(:P1_START_DATE,'DD-MON-YY')

But why is it that on first time load and without clicking on submit button i see date format as '24-JAN-13' in session??

I have kept format as 'DD-MON-YYYY' everywhere
  • 1. Re: issue with date picker date format on page load
    matthew_morris Expert
    Currently Being Moderated
    I have a date picker (P1_START_DATE) on my page with format mask 'DD-MON-YYYY'. I have an on load before header process which loads into this item.
    SELECT TO_DATE(SYSDATE, 'DD-MON-YYYY') INTO :P1_START_DATE FROM DUAL;
    SYSDATE is already a date. You do not issue TO_DATE on a date. Nothing is being done on the SYSDATE because there is nothing for Oracle to do. If you have a date value that you want displayed in a specific character format, you issue TO_CHAR, i.e.:

    SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY') INTO :P1_START_DATE FROM DUAL;
  • 2. Re: issue with date picker date format on page load
    Howard (... in Training) Pro
    Currently Being Moderated
    (Re: What Matthew pointed out.) Very interesting!
    SELECT TO_DATE(SYSDATE, 'DD-MON-YYYY') INTO :P1_START_DATE FROM DUAL;
    I know what I get from this because I tried it -- but I wonder how it does what it does.

    Is SYSDATE (implicitly) converted to character to match the arguments of TO_DATE -- using the NLS format setting? Then is that converted back to a date by TO_DATE?

    I guess it must have produce someing like 01/24/13 in :P1_START_DATE. So you've already lost the century.

    (Did you ever look at the session state to see what the value was for :P1_START_DATE.

    Very interesting.

    Regards,
    Howard
  • 3. Re: issue with date picker date format on page load
    fac586 Guru
    Currently Being Moderated
    Howard (DBA in Training) wrote:
    (Re: What Matthew pointed out.) Very interesting!
    SELECT TO_DATE(SYSDATE, 'DD-MON-YYYY') INTO :P1_START_DATE FROM DUAL;
    I know what I get from this because I tried it -- but I wonder how it does what it does.

    Is SYSDATE (implicitly) converted to character to match the arguments of TO_DATE -- using the NLS format setting? Then is that converted back to a date by TO_DATE?
    Yes.
  • 4. Re: issue with date picker date format on page load
    matthew_morris Expert
    Currently Being Moderated
    I know what I get from this because I tried it -- but I wonder how it does what it does.
    Is SYSDATE (implicitly) converted to character to match the arguments of TO_DATE -- using the NLS format setting?
    Dates in Oracle are stored internally as numbers. The digits to the left of the decimal are the number of days since some date in the past that I don't recall offhand and don't care enough about to Google right now. The digits to the right are the percentage of 24-hours that equals the time of day (i.e. 0 is exactly midnight 00:00:00, .25 is 6:00:00 AM, .5 is exactly noon 12:00:00, and .75 is 6:00:00 PM).

    This is why math work with dates. SYSDATE - 1 is exactly 24 hours prior to now. SYSDATE + 1 is exactly 24 hours from now. TRUNC(SYSDATE) eliminates the decimal portion of the number and makes the time today at 00:00:00.

    When Oracle is asked to place a date into a field that it recognizes as a character, it implicitly converts using the NLS_DATE_FORMAT to display the date as a character. If a character value is used to insert or update a DATE field, Oracle tries to convert that character into a date -- again using NLS_DATE_FORMAT. It doesn't always succeed.

    In the OPs case, the field contained a two-digit year (24-JAN-13), but from his post, the field was set to DD-MON-YYYY format, so Oracle treated the two digits like a four-digit year (i.e. 13 became 0013 ... which is still 13). Had the date been in the field as 24-JAN-213 (presumably due to a typo), the value would have become 24-JAN-0213 (I think... never tested since it's rather useless trivia).
  • 5. Re: issue with date picker date format on page load
    skapex Newbie
    Currently Being Moderated
    Sorry i have used to_char only.
    SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY') INTO :P1_START_DATE FROM DUAL;

    The date picker date is displayed as 'DD-MON-YYYY' while in session it shows 'DD-MON-YY'. But when i submit the page by clicking submit button format in session is 'DD-MON-YYYY'.

    Edited by: skapex on Jan 24, 2013 10:26 PM
  • 6. Re: issue with date picker date format on page load
    matthew_morris Expert
    Currently Being Moderated
    Sorry i have used to_char only.
    SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY') INTO :P1_START_DATE FROM DUAL;
    I'm not sure what you mean. Are you saying that your original post was incorrect and that in Apex you are actually using TO_CHAR? If so, theoretically this should work so what is causing the problem becomes unclear.
    The date picker date is displayed as 'DD-MON-YYYY' while in session it shows 'DD-MON-YY'.
    But when i submit the page by clicking submit button format in session is 'DD-MON-YYYY'.
    The format you place on a date page item does two things.

    1. If the field is a database column and it gets populated with a row fetch, then the date value will be displayed in that format.
    2. If the field is a database column, then when the page is submitted, the format string is used to perform an explicit TO_DATE rather than using the NLS_DATE_FORMAT.

    The date format does nothing to prevent you from putting anything in session state for that field in-between the fetch and submit. If you have a PL/SQL process that performs *:P1_START_DATE := 'Fred Flintstone'*, then the session state for that item will show as Fred Flintstone. Mind you, if you try to submit a date column while that's in session state, Apex will return an error.

    Put another way, if the date format on that field is 'DD-MON-YYYY' but there is a value in there of format 'DD-MON-YY', then you have some code in your app that is populating the session state with that value, because Apex won't be doing that.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points