This discussion is archived
5 Replies Latest reply: Apr 3, 2013 5:04 AM by sushaant RSS

Issue with date

992072 Newbie
Currently Being Moderated
I am trying to load data from Db A to Db B. My filter is where date@A >= to_date('20130101','YYYYMMDD') and date@A <= to_date('20130131','YYYYMMDD') .But when I check my target table in db B , it only has dates between Jan-01 and Jan-30. No Jan-31. Is this could be a timezone issue or something else? I haven't seen this problem before, so may be it is a question that is kinda dumb.
  • 1. Re: Issue with date
    Justin Cave Oracle ACE
    Currently Being Moderated
    I don't believe this is a globalization problem. Hopefully a helpful moderator will move this over to the SQL and PL/SQL forum shortly...

    An Oracle DATE always has a day and a time component. If you don't specify a time component, the default is midnight. So the query you posted will return rows where the date is between Jan 1 at midnight and Jan 31 at midnight. It will not return anything that happened between 12:00:01 am and 11:59:59 pm on Jan 31.

    If you want to include everything that occurred any time on Jan 31, I suspect that you want
    date@a < date '2013-02-01'
    or
    date@a <= to_date( '2013-01-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS' )
    Justin
  • 2. Re: Issue with date
    1000711 Newbie
    Currently Being Moderated
    Hi can someone help me on this.

    The following query gives the error "ORA-01841: (full) year must be between -4713 and +9999, and not be 0"
    although the value of SCREEN_ENTRY_VALUE is '2008/12/31 00:00:00' which is in the correct format given in the to_date clause.
    The same clause is working in the select part but not in the where part of the query

    "TO_DATE(SCREEN_ENTRY_VALUE,'YYYY/MM/DD HH24:MI:SS')"


    The Query------------------------------------------------------------------------------------------------------------------------
    SELECT PEEF1.ASSIGNMENT_ID,
    PETF1.ELEMENT_NAME,
    PIV1.NAME,
    SCREEN_ENTRY_VALUE,
    TO_DATE(SCREEN_ENTRY_VALUE,
    'YYYY/MM/DD HH24:MI:SS') DATE1
    FROM PAY_ELEMENT_ENTRIES_F PEEF1,
    PAY_ELEMENT_LINKS_F PELF1,
    PAY_ELEMENT_TYPES_F PETF1,
    PAY_ELEMENT_TYPES_F_TL PETFT1,
    PAY_ELEMENT_ENTRY_VALUES_F PEEVF1,
    PAY_INPUT_VALUES_F PIV1
    WHERE PEEF1.EFFECTIVE_START_DATE BETWEEN PEEF1.EFFECTIVE_START_DATE AND
    PEEF1.EFFECTIVE_END_DATE
    AND PEEF1.ELEMENT_LINK_ID = PELF1.ELEMENT_LINK_ID
    AND PEEF1.EFFECTIVE_START_DATE BETWEEN PELF1.EFFECTIVE_START_DATE AND
    PELF1.EFFECTIVE_END_DATE
    AND PELF1.ELEMENT_TYPE_ID = PETF1.ELEMENT_TYPE_ID
    AND PETF1.ELEMENT_NAME = 'Prod Pay'
    AND PETF1.ELEMENT_TYPE_ID = PETFT1.ELEMENT_TYPE_ID
    AND PETFT1.LANGUAGE = USERENV('LANG')
    AND PEEVF1.ELEMENT_ENTRY_ID = PEEF1.ELEMENT_ENTRY_ID
    AND PEEF1.EFFECTIVE_START_DATE BETWEEN PEEVF1.EFFECTIVE_START_DATE AND
    PEEVF1.EFFECTIVE_END_DATE
    AND PEEVF1.INPUT_VALUE_ID = PIV1.INPUT_VALUE_ID
    AND PIV1.NAME = 'Date Earned Override'
    AND PEEF1.ASSIGNMENT_ID = 101
    AND TO_DATE('31-DEC-2008',
    'DD-MON-YYYY') BETWEEN PEEF1.EFFECTIVE_START_DATE AND
    PEEF1.EFFECTIVE_END_DATE
    AND TO_DATE(SCREEN_ENTRY_VALUE,'YYYY/MM/DD HH24:MI:SS') < TRUNC(SYSDATE) -- This gives the error "ORA-01841: (full) year must be between -4713 and +9999, and not be 0"
    ORDER BY PEEF1.ASSIGNMENT_ID,
    PETF1.ELEMENT_NAME,
    PIV1.NAME;
  • 3. Re: Issue with date
    S10390 Journeyer
    Currently Being Moderated
  • 4. Re: Issue with date
    sushaant Newbie
    Currently Being Moderated
    try replacing to_date('20130131','YYYYMMDD') to to_date('20130201','YYYYMMDD') or provide complete date string to_date('01/02/2013 00:00:00','dd/mm/yyyy hh24:mi:ss') with less than command. This should work

    Cheers
    Sush
  • 5. Re: Issue with date
    Paul Horth Expert
    Currently Being Moderated
    Do not hijack someone else's thread. start one of your own, referencing this one if necessary.

Legend

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