6 Replies Latest reply on Mar 23, 2021 4:10 AM by Nico Heinze

    Problem with 2 digit dates converting to 4 digit

    Brad Duckett New Member

      I'm having an issue with converting dates when they come in with 2 digit years - e.g. 05/13/21. We have 32 different vendor sending invoices with multiple date formats embedded and I'm writing an reusable expression transformation to decipher date strings and convert them to date in a Oracle database. I have 64 cases with various masks.

       

      Here's a sample of the code:

      DECODE (TRUE,

      ISNULL(DATE_STRING_IN),NULL,

      ...

      IS_DATE(DATE_STRING_IN,'MM/DD/YYYY HH24:MI:SS' ),TO_DATE (DATE_STRING_IN,'MM/DD/YYYY HH24:MI:SS'),

      ...

      IS_DATE(DATE_STRING_IN,'MM/DD/YY HH24:MI:SS' ),TO_DATE (DATE_STRING_IN,'MM/DD/RR HH24:MI:SS'),

      ...

      IS_DATE(DATE_STRING_IN,'MM/DD/YY'),TO_DATE (DATE_STRING_IN,'MM/DD/RR'),

      )

       

      The results of these three lines are:

      Incoming Date String          Oracle date in default format         Result of TO_CHAR(CONVERTED_DATE_V,'MM/DD/RR HH24:MI:SS')

      05/13/2021 15:14:21           13-MAY-21 15:14:21                     05/13/2121 15:14:21 <-- Why 2121? - Shouldn't RR return 2021?

      05/13/21 15:14:21               15-MAY-21 15:14:21                     05/13/2121 15:14:21 <-- This produces the wrong date in the database. 2 days off.

      05/13/2021                          13-MAY-21 00:00:00                     05/13/2121 00:00:00 <-- Same RR questions as above.

       

      In all cases RR produces 2121, it's 100 years off. (I'm over 2020 too, but not by that much!)

       

      Thanks for you help!

        • 1. Re: Problem with 2 digit dates converting to 4 digit
          Nico Heinze Guru

          As of the Transformation Language reference, 'RR' should set the century digits to the nearest century. From this point of view I understand why you are puzzled with the RR thing, that doesn't look right.

          However, the port CONVERTED_DATE_V is not listed exactly, and we don't know the exact list and order of IS_DATE() invocations in your DECODE() function.

          Do I assume correctly that you assign the return value of the DECODE() function to CONVERTED_DATE_V?

          Also, could you please post the complete DECODE() expression here? I have a (very weak) idea what might go wrong here, but I need the whole expression term to verify or rectify.

           

          Regards,

          Nico

          • 2. Re: Problem with 2 digit dates converting to 4 digit
            Brad Duckett New Member

            Nico,

            Thanks for getting back to me. Here's more information including the entire DECODE statement. Here are the ports:

            1. DATE_STRING_IN is an input port of a date in string format from a flat file

            2. CONVERTED_DATE_V is a variable port that has the DECODE below.

            3. CONVERTED_DATE_OUT is a date/time output port that passes the date to the next transformation

            4. TO_CHAR_DATE_STRING is a test field that converts CONVERTED_DATE_V into whatever format I'm testing. It will disappear once we have this sorted out.

             

            Again, thanks!

             

            DECODE (TRUE,

            ISNULL(DATE_STRING_IN),NULL,

            -- Long form 24 hour, four Ys

            IS_DATE(DATE_STRING_IN,'MM DD YYYY HH24:MI:SS' ),TO_DATE (DATE_STRING_IN,'MM DD YYYY HH24:MI:SS'),

            IS_DATE(DATE_STRING_IN,'MM/DD/YYYY HH24:MI:SS' ),TO_DATE (DATE_STRING_IN,'MM/DD/YYYY HH24:MI:SS'),

            IS_DATE(DATE_STRING_IN,'MM-DD-YYYY HH24:MI:SS' ),TO_DATE (DATE_STRING_IN,'MM-DD-YYYY HH24:MI:SS'),

            IS_DATE(DATE_STRING_IN,'MM_DD_YYYY HH24:MI:SS' ),TO_DATE (DATE_STRING_IN,'MM_DD_YYYY HH24:MI:SS'),

             

             

            -- Long form 24 hour, two Ys

            IS_DATE(DATE_STRING_IN,'MM DD YY HH24:MI:SS' ),TO_DATE (DATE_STRING_IN,'MM DD RR HH24:MI:SS'),

            IS_DATE(DATE_STRING_IN,'MM/DD/YY HH24:MI:SS' ),TO_DATE (DATE_STRING_IN,'MM/DD/RR HH24:MI:SS'),

            IS_DATE(DATE_STRING_IN,'MM-DD-YY HH24:MI:SS' ),TO_DATE (DATE_STRING_IN,'MM-DD-RR HH24:MI:SS'),

            IS_DATE(DATE_STRING_IN,'MM_DD_YY HH24:MI:SS' ),TO_DATE (DATE_STRING_IN,'MM_DD_RR HH24:MI:SS'),

             

             

            -- Long form 24 hour, four Ys, European

            IS_DATE(DATE_STRING_IN,'DD MM YYYY HH24:MI:SS' ),TO_DATE (DATE_STRING_IN,'DD MM YYYY HH24:MI:SS'),

            IS_DATE(DATE_STRING_IN,'DD/MM/YYYY HH24:MI:SS' ),TO_DATE (DATE_STRING_IN,'DD/MM/YYYY HH24:MI:SS'),

            IS_DATE(DATE_STRING_IN,'DD-MM-YYYY HH24:MI:SS' ),TO_DATE (DATE_STRING_IN,'DD-MM-YYYY HH24:MI:SS'),

            IS_DATE(DATE_STRING_IN,'DD_MM_YYYY HH24:MI:SS' ),TO_DATE (DATE_STRING_IN,'DD_MM_YYYY HH24:MI:SS'),

             

             

            -- Long form 24 hour, two Ys, European

            IS_DATE(DATE_STRING_IN,'DD MM YY HH24:MI:SS' ),TO_DATE (DATE_STRING_IN,'DD MM RR HH24:MI:SS'),

            IS_DATE(DATE_STRING_IN,'DD/MM/YY HH24:MI:SS' ),TO_DATE (DATE_STRING_IN,'DD/MM/RR HH24:MI:SS'),

            IS_DATE(DATE_STRING_IN,'DD-MM-YY HH24:MI:SS' ),TO_DATE (DATE_STRING_IN,'DD-MM-RR HH24:MI:SS'),

            IS_DATE(DATE_STRING_IN,'DD_MM_YY HH24:MI:SS' ),TO_DATE (DATE_STRING_IN,'DD_MM_RR HH24:MI:SS'),

             

             

            -- Long form 12 hour, four Ys

            IS_DATE(DATE_STRING_IN,'MM DD YYYY HH:MI:SS AM' ),TO_DATE (DATE_STRING_IN,'MM DD YYYY HH:MI:SS AM'),

            IS_DATE(DATE_STRING_IN,'MM/DD/YYYY HH:MI:SS AM' ),TO_DATE (DATE_STRING_IN,'MM/DD/YYYY HH:MI:SS AM'),

            IS_DATE(DATE_STRING_IN,'MM-DD-YYYY HH:MI:SS AM' ),TO_DATE (DATE_STRING_IN,'MM-DD-YYYY HH:MI:SS AM'),

            IS_DATE(DATE_STRING_IN,'MM_DD_YYYY HH:MI:SS AM' ),TO_DATE (DATE_STRING_IN,'MM_DD_YYYY HH:MI:SS AM'),

             

             

            -- Long form 12 hour, two Ys

            IS_DATE(DATE_STRING_IN,'MM DD YY HH:MI:SS AM' ),TO_DATE (DATE_STRING_IN,'MM DD RR HH:MI:SS AM'),

            IS_DATE(DATE_STRING_IN,'MM/DD/YY HH:MI:SS AM' ),TO_DATE (DATE_STRING_IN,'MM/DD/RR HH:MI:SS AM'),

            IS_DATE(DATE_STRING_IN,'MM-DD-YY HH:MI:SS AM' ),TO_DATE (DATE_STRING_IN,'MM-DD-RR HH:MI:SS AM'),

            IS_DATE(DATE_STRING_IN,'MM_DD_YY HH:MI:SS AM' ),TO_DATE (DATE_STRING_IN,'MM_DD_RR HH:MI:SS AM'),

             

             

            -- Long form 12 hour, four Ys, European

            IS_DATE(DATE_STRING_IN,'DD MM YYYY HH:MI:SS AM' ),TO_DATE (DATE_STRING_IN,'DD MM YYYY HH:MI:SS AM'),

            IS_DATE(DATE_STRING_IN,'DD/MM/YYYY HH:MI:SS AM' ),TO_DATE (DATE_STRING_IN,'DD/MM/YYYY HH:MI:SS AM'),

            IS_DATE(DATE_STRING_IN,'DD-MM-YYYY HH:MI:SS AM' ),TO_DATE (DATE_STRING_IN,'DD-MM-YYYY HH:MI:SS AM'),

            IS_DATE(DATE_STRING_IN,'DD_MM_YYYY HH:MI:SS AM' ),TO_DATE (DATE_STRING_IN,'DD_MM_YYYY HH:MI:SS AM'),

             

             

            -- Long form 12 hour, two Ys, European

            IS_DATE(DATE_STRING_IN,'DD MM YY HH:MI:SS AM' ),TO_DATE (DATE_STRING_IN,'DD MM RR HH:MI:SS AM'),

            IS_DATE(DATE_STRING_IN,'DD/MM/YY HH:MI:SS AM' ),TO_DATE (DATE_STRING_IN,'DD/MM/RR HH:MI:SS AM'),

            IS_DATE(DATE_STRING_IN,'DD-MM-YY HH:MI:SS AM' ),TO_DATE (DATE_STRING_IN,'DD-MM-RR HH:MI:SS AM'),

            IS_DATE(DATE_STRING_IN,'DD_MM_YY HH:MI:SS AM' ),TO_DATE (DATE_STRING_IN,'DD_MM_RR HH:MI:SS AM'),

             

             

            -- Short form, four Ys, American

            IS_DATE(DATE_STRING_IN,'MM DD YYYY'),TO_DATE (DATE_STRING_IN,'MM DD YYYY'),

            IS_DATE(DATE_STRING_IN,'MM_DD_YYYY'),TO_DATE (DATE_STRING_IN,'MM_DD_YYYY'),

            IS_DATE(DATE_STRING_IN,'MM/DD/YYYY'),TO_DATE (DATE_STRING_IN,'MM/DD/YYYY'),

            IS_DATE(DATE_STRING_IN,'MM-DD-YYYY'),TO_DATE (DATE_STRING_IN,'MM-DD-YYYY'),

            IS_DATE(DATE_STRING_IN,'Month YYYY'),TO_DATE (DATE_STRING_IN,'Month YYYY'),

            IS_DATE(DATE_STRING_IN,'MON YYYY'),TO_DATE (DATE_STRING_IN,'MON YYYY'),

             

             

            -- Short form, two Ys, American

            IS_DATE(DATE_STRING_IN,'MM DD YY'),TO_DATE (DATE_STRING_IN,'MM DD RR'),

            IS_DATE(DATE_STRING_IN,'MM_DD_YY'),TO_DATE (DATE_STRING_IN,'MM_DD_RR'),

            IS_DATE(DATE_STRING_IN,'MM/DD/YY'),TO_DATE (DATE_STRING_IN,'MM/DD/RR'),

            IS_DATE(DATE_STRING_IN,'MM-DD-YY'),TO_DATE (DATE_STRING_IN,'MM-DD-RR'),

            IS_DATE(DATE_STRING_IN,'Month YY'),TO_DATE (DATE_STRING_IN,'Month RR'),

            IS_DATE(DATE_STRING_IN,'MON YY'),TO_DATE (DATE_STRING_IN,'MON RR'),

             

             

            -- Short form, four Ys, European 

            IS_DATE(DATE_STRING_IN,'DD MM YYYY'),TO_DATE (DATE_STRING_IN,'DD MM YYYY'),

            IS_DATE(DATE_STRING_IN,'DD_MM_YYYY'),TO_DATE (DATE_STRING_IN,'DD_MM_YYYY'),

            IS_DATE(DATE_STRING_IN,'DD/MM/YYYY'),TO_DATE (DATE_STRING_IN,'DD/MM/YYYY'),

            IS_DATE(DATE_STRING_IN,'DD-MM-YYYY'),TO_DATE (DATE_STRING_IN,'DD-MM-YYYY'),

            IS_DATE(DATE_STRING_IN,'YYYY Month'),TO_DATE (DATE_STRING_IN,'YYYY Month'),

            IS_DATE(DATE_STRING_IN,'YYYY MON'),TO_DATE (DATE_STRING_IN,'YYYY MON'),

             

             

            -- Short form, two Ys, European

            IS_DATE(DATE_STRING_IN,'DD MM YY'),TO_DATE (DATE_STRING_IN,'DD MM RR'),

            IS_DATE(DATE_STRING_IN,'DD_MM_YY'),TO_DATE (DATE_STRING_IN,'DD_MM_RR'),

            IS_DATE(DATE_STRING_IN,'DD/MM/YY'),TO_DATE (DATE_STRING_IN,'DD/MM/RR'),

            IS_DATE(DATE_STRING_IN,'DD-MM-YY'),TO_DATE (DATE_STRING_IN,'DD-MM-RR'),

            IS_DATE(DATE_STRING_IN,'YY Month'),TO_DATE (DATE_STRING_IN,'RR Month'),

            IS_DATE(DATE_STRING_IN,'YY MON'),TO_DATE (DATE_STRING_IN,'RR MON'),

             

             

            -- Short form year first, four Ys

            IS_DATE(DATE_STRING_IN,'YYYY MM DD'),TO_DATE (DATE_STRING_IN,'YYYY MM DD'),

            IS_DATE(DATE_STRING_IN,'YYYY_MM_DD'),TO_DATE (DATE_STRING_IN,'YYYY_MM_DD'),

            IS_DATE(DATE_STRING_IN,'YYYY/MM/DD'),TO_DATE (DATE_STRING_IN,'YYYY/MM/DD'),

            IS_DATE(DATE_STRING_IN,'YYYY-MM-DD'),TO_DATE (DATE_STRING_IN,'YYYY-MM-DD'),

             

             

            -- Short form year first, two Ys

            IS_DATE(DATE_STRING_IN,'YY MM DD'),TO_DATE (DATE_STRING_IN,'RR MM DD'),

            IS_DATE(DATE_STRING_IN,'YY_MM_DD'),TO_DATE (DATE_STRING_IN,'RR_MM_DD'),

            IS_DATE(DATE_STRING_IN,'YY/MM/DD'),TO_DATE (DATE_STRING_IN,'RR/MM/DD'),

            IS_DATE(DATE_STRING_IN,'YY-MM-DD'),TO_DATE (DATE_STRING_IN,'RR-MM-DD')

             

             

            )

            • 3. Re: Problem with 2 digit dates converting to 4 digit
              Brad Duckett New Member

              Hi! This issue is still outstanding. Does anyone have ideas? I'm still hung up.

               

              Thank you!

              Brad

              • 4. Re: Problem with 2 digit dates converting to 4 digit
                Nico Heinze Guru

                Hi Brad,

                 

                sorry, slightly busy these weeks...

                According to your DECODE() chain the first case ('05/13/2021 15:14:21') must be processed correctly to TO_DATE(..., 'MM/DD/YYYY HH24:MI:SS') which is the second case in the DECODE() chain.

                 

                Could you please try to run the session with only these three input strings and the EXP set to Verbose Data? I would like to examine the session log to see what exact values are processed and transported.

                 

                Thanks and regards,

                Nico

                • 5. Re: Problem with 2 digit dates converting to 4 digit
                  Eric Vardeman New Member

                  Hi Brad,

                   

                  did you ever figure out how to fix this issue? I just started encountering this issue as well.

                  • 6. Re: Problem with 2 digit dates converting to 4 digit
                    Nico Heinze Guru

                    What would be interesting to know is whether the IS_DATE / TO_DATE combination works correctly or not. It may be that the date/time value is processed in the wrong way later on in the mapping.

                     

                    Can you please turn on Verbose Data for this particular Expression transformation?

                    And make sure that you run the session with only three test values, the wrong one plus two which work fine?

                    Then we have a better chance of finding out what's going wrong.

                     

                    Regards,

                    Nico