How the values are returned from the database depends on the data type used in the underlying database table. There are notably some timestamp data types that do not store milliseconds or time zone information, which may be why your data lacks them when reading it back.
My recommendation is that you use the DateTimeConverter on both input sources to set them to the exact same format before using the ChangeDetector.
How the values are returned from the database depends on the data type used in the underlying database table. There are notably some timestamp data types that do not store milliseconds or time zone information, which may be why your data lacks them when reading it back.
My recommendation is that you use the DateTimeConverter on both input sources to set them to the exact same format before using the ChangeDetector.
Even when they are varchar?
But I'll try with the DateTimeConverter, thanks! :-)
If the storage data type is varchar, then it seems something is truncating your string. Are there any warnings in the FME log when writing the time stamps? How long is the varchar in the table definition, is it long enough?
In the MS-SQL db it's ok
in the data inspector both modificationDate's are fme_varchar as well
There are no warnings in the logs.
Checked with a Logger.
RevisedValue (from API)
Attribute(encoded: UTF-8): `modificationDate' has value `2019-12-03T07:54:20.000+0000'
OriginalValue (from DB)
Attribute(encoded: UTF-16LE): `modificationDate' has value `2019-12-03T08:08:17.000+0000'
The only thing I see is the encoding, could that be an issue?
Update:
AttributeEncoder works but does not help :-)
Attribute(encoded: UTF-8) : `modificationDate' has value `2019-12-03T08:14:46.000+0000'
The encoding should be OK, as long as both are in some sort of UTF (unicode) variant then it shouldn't be a problem. Anyways, I don't suppose you have any special characters in the time stamps.
How is the time stamp text field defined in the FME reader? Does it correspond to the database table definition? Perhaps try recreating the reader.
If that doesn't help, try inspecting the reader output before any of the other transformers.
The encoding should be OK, as long as both are in some sort of UTF (unicode) variant then it shouldn't be a problem. Anyways, I don't suppose you have any special characters in the time stamps.
How is the time stamp text field defined in the FME reader? Does it correspond to the database table definition? Perhaps try recreating the reader.
If that doesn't help, try inspecting the reader output before any of the other transformers.
In the reader it's defined varchar(30), same as in DB.
recreated it but no difference.
The logger/inspector were connected directly to the reader, there it was just fine. The moment it hits the ChangeDetector it's fubar.
So for the fun of it I swithced Orignal and Revised ports and guess what, now the API is truncated with the Original Port.
Could it be a bug?
In the reader it's defined varchar(30), same as in DB.
recreated it but no difference.
The logger/inspector were connected directly to the reader, there it was just fine. The moment it hits the ChangeDetector it's fubar.
So for the fun of it I swithced Orignal and Revised ports and guess what, now the API is truncated with the Original Port.
Could it be a bug?
That's really strange. What FME version is this?
That's really strange. What FME version is this?
2019.0.2.0 (20190605 - build 19260)
2019.0.2.0 (20190605 - build 19260)
I'm unable to reproduce anything like it using FME 2019.2.1.
Can you reproduce the issue with a minimal workspace and post it here?
Are you able to try upgrading to 2019.2.1 and try again?
I'm unable to reproduce anything like it using FME 2019.2.1.
Can you reproduce the issue with a minimal workspace and post it here?
Are you able to try upgrading to 2019.2.1 and try again?
It's working now but I found that when I use a certain attribute in
Attribute Matching strategy -> Selected Attributes the modificationDate is truncated; If I dont't use that attribute it works like a charm?
It's working now but I found that when I use a certain attribute in
Attribute Matching strategy -> Selected Attributes the modificationDate is truncated; If I dont't use that attribute it works like a charm?
Without seeing the workspace and the data it's very difficult to say, but in general I find that behavior surprising. I would consider opening a support case with Safe.
Without seeing the workspace and the data it's very difficult to say, but in general I find that behavior surprising. I would consider opening a support case with Safe.
David, thank your for the feedback!
How the values are returned from the database depends on the data type used in the underlying database table. There are notably some timestamp data types that do not store milliseconds or time zone information, which may be why your data lacks them when reading it back.
My recommendation is that you use the DateTimeConverter on both input sources to set them to the exact same format before using the ChangeDetector.
Yes this is what I do when comparing times across differing systems of record, cast the values to a common precision like %Y%m%d%H%M%S
Yes this is what I do when comparing times across differing systems of record, cast the values to a common precision like %Y%m%d%H%M%S
Normally yes, but the data is stored as varchar.
