Access stores the Date/Time data type as a double-precision, floating-point number up to 15 decimal places. It stores dates before December 30, 1899 as negative numbers...
Is it the conversion issue from FME date definition to Access definition? Seems like it only supports positive numbers (later than 30.12.1899)?
@zuzanna_sz It looks like this is an issue with FME. What version of FME are you using?
As a workaround, would it be possible to write to a string field, then use ALTER TABLE to change the string field to a DATE field?
@Mark Stoakes I am using 2020.1.2.0 (20200902 - Build 20620 - WIN64). Thanks for the hint, I am already using a short text to manage that but it complicates the workflow a lot each time (and I am writing to access very very often recently).
@Mark Stoakes I am using 2020.1.2.0 (20200902 - Build 20620 - WIN64). Thanks for the hint, I am already using a short text to manage that but it complicates the workflow a lot each time (and I am writing to access very very often recently).
@zuzanna_sz If you use the FeatureWriter transformer to write your data, then you can use the output form the Summary port to trigger a SQLExecutor to alter your table. Then it would all be in a single workspace. I think that should work OK.
Hi @Mark Stoakes and @zuzanna_sz
It will work with the method provided by Mark, but you may need to create a DB first to add SQLCreator. I was able to resolve the problem by adding a sampler to get the first feature and pass it into a FeatureWriter to write one record with field type 'date' and using another Writer (or FeatureWriter) to truncate the table and write all the records as 'text'.
Demo workspace (Writing_Older_Dates.fmw) is attached for further clarification.