You have the negate value ticked there, so at the moment you are only trying to format dates that don't have a value.

You need the first test to be testing for if it has a value, and formatting if that is true, then everything else gets set to null

.

if I remove the negate value tick it still returns nulls as 1900-01-01 00:00:00 . I put the tick in the negate value in the hope of getting the only the date values formatted and the nulls ignored.

Do you see the 1900-01-01 00:00:00 in FME or the format you are writing to?

What version of FME are you using, looks like it's older as that negate box doesn't exist now?

Do you see the 1900-01-01 00:00:00 in FME or the format you are writing to?

What version of FME are you using, looks like it's older as that negate box doesn't exist now?

This still returns null as 1900-01-01 00:00:00

Make sure that date/time format of comnDate is proper. The @DateTimeFormat function requires that entered date/time value is formatted with Standard FME Date/Time Format.

The date/time format is correct and works for all the other date fields in my dataset . The date values in the comnDate format how I expect them. it is just this problem with the null values .

@lizzygradidge Use the NullAttributeMapper to make sure your missing or NULL values are actually <null> and not the work NULL. then pass that thru the date formatting

I'd start to debug this by putting down a TestFilter with a series of tests, to test for Attribute is Null, Attribute is Empty, and Attribute is Missing. That way you can see which - if any - FME thinks it is.

I tested a subset of 39 records , 6 of which had dates , 33 were null.

FME sees these as <null>

OK, so those are definitely nulls. Hmmm. I'm trying your scenario (I'm using FME2020) and don't get the same issue. Which version are you using?

For the moment... I suggest now that we've got the data split by a Tester, that you apply the date formatting to the failed features (the ones with a date) and then the existing nulls won't be affected. That will get your workspace doing what you need and you won't need to use a conditional statement.

But I'd still like to figure out what's going wrong. Can you share your workspace and a sample of data for us to try? If necessary - for confidentiality reasons - you could send it to our support team (via safe.com/support) where we can look at it in private.

Like I mentioned, it works fine for me if I have this in an AttributeManager:

So either there's something else in your workspace causing the problem, or there's a bug in FME that something in your workspace triggers (whereas on mine it doesn't). Either way, that's why it would help to have a copy of your workspace to check out.

I hope this helps. Apologies for the inconvenience this must be causing.

I have verified that it is not the source data that is incorrect or not truly null. If I run a simple translation using the advice above then the data returns dates as dates and null values as null.

output viewed in Excel:

In my fullworkbench translation it would appear that the Attribute_Exposer_3 seems to replace null values with 1900-01-01 00:00:00 .

In the above I use a StringConcatenator_4 to build a complex union query for the SqlExecutor_8 to execute. (I tried to do this in sqlExecutor but it would not run successfully so had help with using StringConcatenator).

The Sql query returns the null values as expected in sqlstudio.

I can not expose the attributes in SqlExecutor_8 as this only runs the _result from StringConcatenator and so can not populate from the sql query

Hence I use an AttributeExposer to provide all the required attributes. It is here in the AttributeExposer_3_ output inspector that the null are replaced by 1900-01-01 00:00:00

This is only happening for these two date fields complDate and comnDate. The targetDate is returning null as expected.

sqlstudio:

FME:

Any further advice much appreciated.

Lizzy

I will attempt a copy of the workspace for you tomorrow if my above description is not sufficiently clear .

thanks for your help.

Lizzy

it is running the result from StringConcatenator_4.

For some reason the query was too complex for SqlExecutor but it happily runs the result from the StringConcatenator.