2 Replies Latest reply on Aug 10, 2020 7:06 AM by Cyber King

    AM or PM to_date

    Cyber King Active Member

      Hi ,

      Source date format will be 22/05/2020 11:23:55 PM

       

      Inbetween im using expression TO_DATE(DATE_COLUMN,'MM/DD/YYYY HH:MI:SS PM)

       

      expected output will be same as source..

      but scenario is...

      If I receive AM instead of PM, how to handle this using TO_DATE function ?

        • 1. Re: AM or PM to_date
          Krishna Gangwar Active Member

          Hi,

           

          Can you try below DECODE expression?

           

          DECODE( TRUE,

           

          --test first format

             IS_DATE(DATE_COLUMN,'MM/DD/YYYY HH:MI:SS PM),

           

           

          --if true, convert to date

                TO_DATE(DATE_COLUMN,'MM/DD/YYYY HH:MI:SS PM),

           

          --test second format; if true, convert to date

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

           

           

          --if none of the above

             ERROR( 'NOT A VALID DATE') )

           

           

          Let me know if this helps.

           

          Regards,

          Krishna

          • 2. Re: AM or PM to_date
            Cyber King Active Member

            Hi ,

            I found different solution.

            If we use like TO_DATE(DATE_COLUMN,'MM/DD/YYYY HH:MI:SS PM) , then automatically if AM came from source it will be consider the AM , if PM came from source then it will be consider as PM..

             

            So we dont need to declare for AM or PM seperately...instead we can declare PM alone then it will consider AM Also from source.

             

            So we can use TO_DATE(DATE_COLUMN,'MM/DD/YYYY HH:MI:SS PM) for both AM and PM , informatica automatically assign AM and PM