Query about handling Null Dates

Hi. I have been experimenting with Reformat Date and If Empty or Null and can't quite figure out how to setup a transformation.

the scenario is that the inbound file has several date columns. Some will have a value of Null (or perhaps an empty string) and others will have a date in MM/DD/YYYY format.

What I wanted to achieve in the transformation was that if the value is Null then just pass it on as Null as that is an acceptable value. If it is Not null then I want to reformat the date into YYYY-MM-DD (For SQL Server database table). If for some reason the reformat fails then I want the transformation to still Log that fact AND Abort.

I attempted to trim the inbound date which if empty will make it Null and then pass to the Reformat Date Function. But this seems to fail if the value is Null whereas I need it to just pass it on. BUT.... If the inbound value is NOT Null but is an invalid date or the format is wrong I need it to Abort.

Hope this makes sense. Thanks again for all of your support.
Cheers

Hi Bruce

The only way I think it can be done right now is to redirect the data flow using a validator

Thanks.

I can see how that would work, except that it may be difficult in my case due to the fact that there are nearly 10 different date values in each row that need to be handled.

I will take a look and see if I can come up with something.

thanks for the insight

I would be interesting to see your sulution

If you don't have a solution yet, here is an option.

Change the Null value to an arbitrary date (01/01/1000)
Reformat the date
Change the reformatted arbitrary date back to Null

Hope this helps

Hi

for anyone coming here later. There is now a check box in a number for Format Actions that allows you to ignore null date values but still process valid dates as well as handling malformed dates.