7 Replies Latest reply on Apr 23, 2021 2:05 PM by Nico Heinze

    Date Format for a string with timezone

    Ashish Gupta Active Member

      Hello Group,

      One of our string fields is of the "2018-12-30T16:00:00-05:00" format, what "format string" should we pass to the "TO_DATE" function to convert this into a datetime?

       

      I tried the following format string along with a few other other combinations but no luck so far.

      TO_DATE(event_instances_start,'YYYY-MM-DD HH24:MI:SS US')

       

      Thanks,

      Ashish

        • 1. Re: Date Format for a string with timezone
          Nico Heinze Guru

          Use this format:

          To_Date( event_instances_start, 'YYYY-MM-DD"T"HH24:MI:SS.US')

          That means: your string is almost correct, you just have to replace the blank between date and time parts by a double quote followed by the uppercase T for the timezone followed by another double quote.

           

          This "trick" with the double quotes to quote fixed strings in a format description is described in the Transformation Language guide for PowerCenter in the chapter about To_Date() and To_Char(date) format strings. It means that the string enclosed in double quotes will simply be skipped in the date/time string.

           

          Regards,

          Nico

          • 2. Re: Date Format for a string with timezone
            SIXTO QUILES New Member

            HI, i need help with a related conversion.

             

            I need to convert this string expression to a date format:

                 date/time EFFECTIVE_DATE = 06/12/2021 00:00:00.000000

                 string       TO_CHAR(EFFECTIVE_DATE,'MM/DD/YYYY') || 'T04:00:00'  -> 06/12/2021T04:00:00

             

            But trying to convert to date/time:

             

            To_Date((TO_CHAR(EFFECTIVE_DATE,'MM/DD/YYYY') || 'T04:00:00'), 'YYYY-MM-DD"T"HH24:MI:SS.US')

             

            The output is: 12/20/0006 01:04:00.000000

             

            Is change month, day, year

            • 3. Re: Date Format for a string with timezone
              Nico Heinze Guru

              Not clear what you need. Do you have an input string which you want to convert to a Date/Time?

               

              Regards,

              Nico

              • 4. Re: Date Format for a string with timezone
                Srini Vemula New Member

                Hello , I am using informatica 10.4.1.

                 

                I have similar requirement where i am getting the date from a file as 2021-04-15T13:21:14.000-05:00

                 

                when i tried to use the same way as you mentioned i am still getting invalid date format to conversion this is how i am going in the informatica, not sure where i am going the mistake

                 

                i_enddate = 2021-04-15T13:21:14.000-05:00

                 

                To_Date(i_enddate, 'YYYY-MM-DD"T"HH24:MI:SS.US')

                • 5. Re: Date Format for a string with timezone
                  Nico Heinze Guru

                  The point is that the format string must match the format of the input string exactly. Which is not the case here because your input string bears this appended timezone shift "-05:00".

                  You have to find out whether an input string contains such a shift and, if yes, "remove" it (e.g. by using the SUBSTR() function to use only the part before the "-").

                  The string before the "-" sign can then become input to TO_DATE().

                   

                  Regards,

                  Nico

                  • 6. Re: Date Format for a string with timezone
                    Srini Vemula New Member

                    Sorry for not understanding, in the original question posted, the date string mentioned is "2018-12-30T16:00:00-05:00"

                     

                    My Date string is i_enddate = 2021-04-15T13:21:14.000-05:00

                     

                    I think both are same format

                     

                    I did not exactly understand the difference here

                     

                    and when your reply to my question, remove the shift, you mean use substring and get 2021-04-15T13:21:14.000 and then convert?

                    • 7. Re: Date Format for a string with timezone
                      Nico Heinze Guru

                      That's exactly what I meant.

                       

                      And yes, you are right, I totally overlooked the -05:00 in the original post. Thanks for pointing it out.

                       

                      Regards,

                      Nico