1 Reply Latest reply on Sep 13, 2021 3:33 AM by Nico Heinze

    date format 1/1/21

    Lakshman Kumar New Member


      I need help to get proper date format.

      Source: flat file

      Date values:   1/1/21, 1/15/21, 11/2/21, 11/12/21


      I need to take this date and look up on dim_Date table in and load the date_key in fact table.

      but Infa is erroring out as date is not proper format. so I want date in always mm/dd/yyyy format.

      I tired by defining source column as string and also as date. but still no luck.

      Please help me.




        • 1. Re: date format 1/1/21
          Nico Heinze Guru

          Unfortunately your date values are read from the source file in a format that PowerCenter doesn't understand (there is simply no date/time format with one character for month or days numbers, at least not that I know of).

          So what you have to do is:

          - Either make sure that the date is always in an understandable format,

          - or to make sure yourself it's usable.


          The first item requires that you convince the people from the source system that they should deliver a "clean" format. Probably not doable, but it might be worth a try.


          The second item requires that you build some mapping logic yourself, like this:

          You use INSTR() in two variable ports to get the positions of the first and the second slash "/" in the input string.

          Then you use SUBSTR() to "extract" the three numbers from the input string (still as strings), based on the INSTR() values.

          Now you convert these three part strings into numbers using TO_INTEGER().

          Finally you use the MAKE_DATE_TIME() function to construct a date/time value from these three numbers:

              Make_Date_Time( v_year, v_month, v_day)

          If this results in a NULL value, then your input string did not show a correct date, otherwise the result will be a Date/Time port.


          Any more questions? Please don't hesitate to ask.