I have an expression which does the below check IIF(IS_DATE(DATESTR1, 'DD-MON-YYYY'), TO_DATE(DATESTR1, 'DD-MON-YYYY'), NULL)
But even if I get an input 07-Nov-12, informatica loads it as 12/07/0012 on an oracle database.
In oracle I could have used FXDD-MON-YYYY. Is there a similar soluition in Informatica? Else I will have to do a Length check forst and then convert
Here you are passing YY for year ,but you are converting it to YYYY.Try changing the expression as below.
IIF(IS_DATE(DATESTR1, 'DD-MON-YY'), TO_DATE(DATESTR1, 'DD-MON-YY'), NULL)
Thank you Sasi...
ok, maybe I haven't explained clearly... I get the data in DD-MON-YYYY format in the file... So the basic check I am doing is make sure its a valid date...for example if I get data like 31-JAN-20223 then I should convert this to NULL and load safely... So this is happening fine in my code
But by mistake the user once sent 31-JAN-22 and then informatica loaded the code as 31-JAN-0022 while I was expecting it to error out...
So I can actually do IIF length of date field<>11 then error out... but I dont want to have this length check on top of IS_DATE check . Can we do it in one shot?
How about something like the following:
IIF(IS_DATE(DATESTR1, 'DD-MON-YYYY') OR GET_DATE_PART(DATESTR1, 'YYYY') > 1900, TO_DATE(DATESTR1, 'DD-MON-YYYY'), NULL)
I haven't tested it, but for any less than 4 digit year, sounds like you get leading 0s, setting the value less than 1900 should take care of any less than 4 digit years getting passed through incorrectly. did a quick correction, make that greater than 1900, adjusting for the IIF exp.