1 2 Previous Next 17 Replies Latest reply on May 17, 2016 9:51 PM by Jarkko Turpeinen

    ORA-01878: specified field not found in datetime or interval

    vpolasa

      Hi

       

         Could you please help me understand why I'm seeing below error? How can I resolve this?

       

          CREATE TABLE MI_DTWKHIST_TST (DATE_D DATE, ENTY_KEY NUMBER);
        
          INSERT INTO MI_DTWKHIST_TST VALUES (TO_DATE('26-MAR-2000 04:01 28','DD-MON-YYYY HH:MI SS'), 101);
          INSERT INTO MI_DTWKHIST_TST VALUES (TO_DATE('26-MAR-2000 05:01 28','DD-MON-YYYY HH:MI SS'), 102);
        
              SELECT to_char(DATE_D, 'dd-mon-yyyy hh:mi ssam'),
          FROM_TZ(TO_TIMESTAMP(DATE_D), 'Asia/Baku'),
                      ENTY_KEY
                         FROM MI_DTWKHIST_TST
                         WHERE enty_key = 101;
      
      
      

       

      ORA-01878: specified field not found in datetime or interval

      01878. 00000 -  "specified field not found in datetime or interval"

      *Cause:    The specified field was not found in the datetime or interval.

      *Action:   Make sure that the specified field is in the datetime or interval.

       

      But when I query for enty_key = 102, I do not get any error:

          SELECT to_char(DATE_D, 'dd-mon-yyyy hh:mi ssam'),
            FROM_TZ(TO_TIMESTAMP(DATE_D), 'Asia/Baku'),
                      ENTY_KEY
                         FROM MI_DTWKHIST_TST 
                         WHERE enty_key = 102;
      

       

      desired o/p:

      TO_CHAR(DATE_D,'DD-MON-YYYYHH:M FROM_TZ(TO_TIMESTAMP(DATE_D),'ASIA/BAKU')   ENTY_KEY
      ------------------------------- ---------------------------------------------------
      26-mar-2000 05:01 28am          26-MAR-00 05.01.28.000000000 AM ASIA/BAKU      102
      

       

      Using Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

      Windows 7

       

      Thanks..

        • 1. Re: ORA-01878: specified field not found in datetime or interval
          John Thorton

          vpolasa wrote:

           

          Hi

            

             Could you please help me understand why I'm seeing below error? How can I resolve this?

           

          1.     CREATE TABLE MI_DTWKHIST_TST (DATE_D DATE, ENTY_KEY NUMBER); 
          2.      
          3.     INSERT INTO MI_DTWKHIST_TST VALUES (TO_DATE('26-MAR-2000 04:01 28','DD-MON-YYYY HH:MI SS'), 101); 
          4.      
          5.         SELECT to_char(DATE_D, 'dd-mon-yyyy hh:mi ssam'), 
          6.     FROM_TZ(TO_TIMESTAMP(ds_tbl.DATE_D), 'Asia/Baku'), 
          7.                 DS_TBL.ENTY_KEY 
          8.                    FROM MI_DTWKHIST_TST ds_tbl 
          9.                    WHERE ds_tbl.enty_key = 101; 

           

          ORA-01878: specified field not found in datetime or interval

          01878. 00000 -  "specified field not found in datetime or interval"

          *Cause:    The specified field was not found in the datetime or interval.

          *Action:   Make sure that the specified field is in the datetime or interval.

           

          Using Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

          Windows 7

           

          Thanks..

          what is "DS_TBL" in posted code above?

          • 2. Re: ORA-01878: specified field not found in datetime or interval
            vpolasa

            It's just an alias to the table. I removed it.

            • 3. Re: ORA-01878: specified field not found in datetime or interval
              Frank Kulash

              Hi,

               

              Review the syntax of TO_TIMESTAMP

              vpolasa wrote:

               

              TO_TIMESTAMP(ds_tbl.DATE_D)

              The 1st argument is supposed to be a character string but you're calling it with a DATE.

              Although it's legal to call TO_TIMESTAMP with just 1 argument, it's simply asking for trouble.  Always pass at least 2 arguments.

              Also review the syntax of FROM_TZ.

               

              Maybe you want something like:

              SELECT  TO_CHAR (date_d, 'dd-mon-yyyy hh:mi ssam')  AS dt

              ,       FROM_TZ ( CAST (ds_tbl.date_d AS TIMESTAMP)

                              , '4:00'

                              )                                   AS tm

              ,       ds_tbl.enty_key

              FROM    mi_dtwkhist_tst  ds_tbl

              WHERE   ds_tbl.enty_key  = 101

              ;

              1 person found this helpful
              • 4. Re: ORA-01878: specified field not found in datetime or interval
                Solomon Yakobson

                As Frank said, you shouldn't be using TO_TIMESTAMP. Use CAST instead. But you will still get same error. Why? Daylight savings time. March 26, 2000 4:00 AM clock in Baku was changed to 5:00 am, so there was no 26-MAR-2000 04:01 28 in Azerbaijan.

                 

                SY.

                • 5. Re: ORA-01878: specified field not found in datetime or interval
                  Stefan Jager

                  I'm too lazy to check if it causes the error, but since it's good practice I'll mention it anyway: You were missing an alias:

                   

                  SELECT to_char(DATE_D, 'dd-mon-yyyy hh:mi ssam'), -- <<= Here you miss ds_tbl
                         FROM_TZ(TO_TIMESTAMP(ds_tbl.DATE_D), 'Asia/Baku'), 
                         DS_TBL.ENTY_KEY 
                  FROM MI_DTWKHIST_TST ds_tbl 
                  WHERE ds_tbl.enty_key = 101; 
                  
                  

                   

                  Either use aliases everywhere, or don't use them at all. Don't mix it.

                   

                  I would also not mix capitals and lowercase like you are doing, but rather write everything using lowercase. That makes it much more readable.

                   

                  Regards,

                  Stefan

                   

                  Edit: never mind, just noticed you've removed the alias. The communities are slow today behind my customer's firewall (or maybe it's just me )

                  1 person found this helpful
                  • 6. Re: ORA-01878: specified field not found in datetime or interval
                    vpolasa

                    Thanks Frank and Solomon..

                     

                    Could you please assist me on how we can resolve this? As I noticed there are few records falling between 4am and 5am.

                    One resolution I can think of is catch these records in the exception block and add 1 hour and apply the from_tz.

                    • 7. Re: ORA-01878: specified field not found in datetime or interval
                      Frank Kulash

                      Hi,

                      vpolasa wrote:

                       

                      Thanks Frank and Solomon..

                       

                      Could you please assist me on how we can resolve this? As I noticed there are few records falling between 4am and 5am.

                      One resolution I can think of is catch these records in the exception block and add 1 hour and apply the from_tz.

                      What, exactly, do you want to do?

                      Post a little sample data (CREATE TABLE and INSERT statements) and the output you want from that sample data.  Do this whenever you have any problem.  See the Forum FAQ: Re: 2. How do I ask a question on the forums?

                      If you're unsure about the exact output you want, post a couple of possibilities, and explain.

                      1 person found this helpful
                      • 8. Re: ORA-01878: specified field not found in datetime or interval
                        vpolasa

                        Frank..

                         

                        I updated my original post. I have few questions:

                         

                        1. This question might be for Solomon: I tried with different times and it works if the time doesn't fall between 4-5. How would we know if this issue was due to daylight saving?

                        2. How can we make the select statement work for enty_key = 101? I'm looking for output similar to enty_key = 102.

                        3. Shouldn't Oracle internally adjust the time so it doesn't fall between the missing hour?

                         

                        Thanks..

                        • 9. Re: Re: ORA-01878: specified field not found in datetime or interval
                          Solomon Yakobson

                          vpolasa wrote:

                           

                          1. This question might be for Solomon: I tried with different times and it works if the time doesn't fall between 4-5. How would we know if this issue was due to daylight saving?

                          2. How can we make the select statement work for enty_key = 101? I'm looking for output similar to enty_key = 102.

                          3. Shouldn't Oracle internally adjust the time so it doesn't fall between the missing hour?

                           

                           

                          1 & 3: Date datatype (same as TIMESTAMP) has no time zone, therefore Oracle has no way of saying/checking if value is valid. For example, March 26, 2000 4:01 AM is valid in New York, London or Paris but not in Baku:

                           

                          SQL> SELECT FROM_TZ(TIMESTAMP '2000-03-26 04:01:00','Asia/Baku') TS FROM DUAL;

                          SELECT FROM_TZ(TIMESTAMP '2000-03-26 04:01:00','Asia/Baku') TS FROM DUAL

                                        *

                          ERROR at line 1:

                          ORA-01878: specified field not found in datetime or interval

                           

                          SQL> SELECT FROM_TZ(TIMESTAMP '2000-03-26 04:01:00','America/New_York') TS FROM DUAL;

                           

                          TS

                          ---------------------------------------------------------------------------

                          26-MAR-00 04.01.00.000000000 AM AMERICA/NEW_YORK

                           

                          SQL> SELECT FROM_TZ(TIMESTAMP '2000-03-26 04:01:00','Europe/London') TS FROM DUAL;

                           

                          TS

                          ---------------------------------------------------------------------------

                          26-MAR-00 04.01.00.000000000 AM EUROPE/LONDON

                           

                          SQL> SELECT FROM_TZ(TIMESTAMP '2000-03-26 04:01:00','Europe/Paris') TS FROM DUAL;

                           

                          TS

                          ---------------------------------------------------------------------------

                          26-MAR-00 04.01.00.000000000 AM EUROPE/PARIS

                           

                          SQL>

                           

                          And for your second question - what output do you expect? Same as for 102?

                           

                          SY.

                          • 10. Re: ORA-01878: specified field not found in datetime or interval
                            vpolasa

                            Yes Solomon.. I'm trying to get the output similar to (not same as) 102.

                            I'm trying to get  a valid output without error if I pass a time between the timezone.

                            I'm still confused, if Oracle knows to throw an error if the timezone falls between the daylight saving time, why wouldn't Oracle adjust the time implicitly?

                            1 person found this helpful
                            • 11. Re: ORA-01878: specified field not found in datetime or interval
                              Solomon Yakobson

                              vpolasa wrote:

                               

                              Yes Solomon.. I'm trying to get the output similar to (not same as) 102.

                              I'm trying to get  a valid output without error if I pass a time between the timezone.

                              I'm still confused, if Oracle knows to throw an error if the timezone falls between the daylight saving time, why wouldn't Oracle adjust the time implicitly?

                              1. We don't have crystal balls - what means "similar"? Please provide exact output you expect.

                              2. Oracle has no crystal ball either - you are telling it to make a timestamp with time zone having value 26-MAR-00 04.01.00.000000000 AM Asia/Baku which doesn't exist.

                               

                              SY.

                              1 person found this helpful
                              • 12. Re: ORA-01878: specified field not found in datetime or interval
                                vpolasa

                                Thanks Solomon, but I'm still confused.


                                If Oracle knows a timestamp in timezone doesn't exist, then why not Oracle adjust time implicitly?

                                 

                                Because this is not just about DST. Few countries have time change (irrelevant to DST). Example: India in 1941, Oct 01 had +36 minutes.

                                 

                                SELECT FROM_TZ(TIMESTAMP '1941-10-01 00:01:00','Asia/Calcutta') TS FROM DUAL;

                                 

                                I'd like to have your expert opinion:

                                How would you resolve this?: A table has a timestamp field with 10 records. One of the record is above mentioned time. How would you convert all the records to India time zone (including the above record)?

                                 

                                Thanks.

                                • 13. Re: ORA-01878: specified field not found in datetime or interval
                                  Jarkko Turpeinen

                                  If Oracle knows a timestamp in timezone doesn't exist, then why not Oracle adjust time implicitly?

                                   

                                   

                                  i explained this once to you but here it is again. Edit: can't see post anymore, deleted or something else?

                                   

                                  Tell oracle how much you want to move the clock. If you use DST name say "Asia/Calcutta" then it does not work but instead give an exact value for conversion.

                                   

                                  alter session set time_zone = "-01:00"

                                  1 person found this helpful
                                  • 14. Re: Re: ORA-01878: specified field not found in datetime or interval
                                    Jarkko Turpeinen

                                    Because this is not just about DST. Few countries have time change (irrelevant to DST). Example: India in 1941, Oct 01 had +36 minutes.

                                     

                                    SELECT sys_extract_utc( from_tz(timestamp '1941-10-01 00:01:00', '+00:36') ) utc from dual;
                                    
                                    
                                    UTC                         
                                    -----------------------------
                                    30.09.1941 23:25:00,000000000
                                    
                                    1 2 Previous Next