Skip navigation
Contact Us
1230 Views 4 Replies Latest reply: Nov 28, 2012 5:19 AM by Gaspard Gabriel RSS
Gaspard Gabriel Newbie 3 posts since
Jan 10, 2012
Currently Being Moderated

Nov 27, 2012 11:11 PM

Limitation with function MAKE_DATE_TIME

Hello members,

 

Can you please kindly advise on the below case.

 

It seems that the function MAKE_DATE_TIME has some limitations as per the case.

 

In a transformation port we use the function likewise :

 

IIF(

 

GET_DATE_PART( IN_Z_DATE_PERTE33, 'DD' )<=15,

 

MAKE_DATE_TIME( GET_DATE_PART( IN_Z_DATE_PERTE33, 'YYYY' ),GET_DATE_PART( IN_Z_DATE_PERTE33, 'MM' ) , 1)

,

MAKE_DATE_TIME( GET_DATE_PART( IN_Z_DATE_PERTE33, 'YYYY' ),GET_DATE_PART( IN_Z_DATE_PERTE33, 'MM' )+1 , 1)

)

In the case when the date parameter is 12/31/2012 00:00:00,the function seems to fail as it can't add one month to this date and by the way switch to the next date (2013/01/01).

 

Is this a known issue in informatica powercenter 8.6.0 ?

 

Thank you for any potential attention to this case.

 

Best regards,

 

Gabriel

  • paluri mahesh Newbie 7 posts since
    Jan 23, 2009
    Currently Being Moderated
    Nov 28, 2012 1:32 AM (in response to Gaspard Gabriel)
    Limitation with function MAKE_DATE_TIME

    Hi Gabriel,

     

    As per my understanding your logic has to work as below.

     

    if the date is with in 1-15 then it has to point current month first day. if it is with in 16-31 it has to point to next month 01.

     

     

    in the case of date 12/31/2012 date is above 16 so it has to fall to next month nothing but 2013/01/01.

     

     

    when we pass the date 12/31/2012 month part it will be evaluated to 13 so the make_to_date will get parameters as 2012,13,01 so it is converting as 2013/01/01.

     

    correct me if i am wrong...

     

    thanks,

    mahesh paluri.

      • paluri mahesh Newbie 7 posts since
        Jan 23, 2009
        Currently Being Moderated
        Nov 28, 2012 4:08 AM (in response to Gaspard Gabriel)
        Limitation with function MAKE_DATE_TIME

        Hi Grabiel,

         

        month parameter of MAKE_DATE_TIME always expected month values b/w 1 to 12.

         

        in your case it can be handled like below:

         

        IIF(

         

        GET_DATE_PART( IN_Z_DATE_PERTE33, 'DD' )<=15,

         

        MAKE_DATE_TIME( GET_DATE_PART( IN_Z_DATE_PERTE33, 'YYYY' ),GET_DATE_PART( IN_Z_DATE_PERTE33, 'MM' ) , 1)

        , iif ( GET_DATE_PART ( IN_Z_DATE_PERTE33, 'MM' )=12,

        MAKE_DATE_TIME( GET_DATE_PART( IN_Z_DATE_PERTE33, 'YYYY' )+1,1, 1),

        MAKE_DATE_TIME( GET_DATE_PART( IN_Z_DATE_PERTE33, 'YYYY' ),GET_DATE_PART( IN_Z_DATE_PERTE33, 'MM' )+1 , 1)

        ))

         

         

        let me know if any thing wrong

         

        thanks,

        mahesh paluri.

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points