5 Replies Latest reply: Apr 3, 2013 7:16 AM by Paul Horth RSS

    Issue with date

    992072
      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
          JustinCave
          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
            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;
            • 4. Re: Issue with date
              sushaant
              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
                Do not hijack someone else's thread. start one of your own, referencing this one if necessary.