I have a project that is updating a SCD type table and uses a datetime field as a condition in an update database table action. The datetime field I’m using has the value 401404.000694444 and been formatted as: yyyy-MM-dd hh:mm
However, I keep getting the following error: “Value in the [existing_valid_to] column at row #1 is not an integer and can’t be matched to a DateTime DB column”
Anyone know how I can get around this?
I should add that the existing_valid_to field is actually sourced from a datetime field in a Db, so can’t fathom why it wouldn’t compare to a value of the same type?
EasyMorph doesn't allow comparison between timestamps because timestamps may include fractions of seconds and may be affected by rounding errors introduced during internal type casting from database data types to EasyMorph data types and / or back. For instance, in your example, 401404.000694444 may become 401404.000694445 (notice the last digit change) and no longer exactly match the original value. To avoid possible matching errors, EasyMorph doesn't support matching timestamps (i.e. date + time) and only supports matching integer dates (i.e. dates without the time part) in all database-related actions.
If you're sure that you can handle possible rounding errors, you can export the new values from EasyMorph into a temporary table and then run in EasyMorph a custom SQL statement UPDATE + SELECT, which will update the target database table with the values from the temporary table. See this link for an example: https://stackoverflow.com/questions/2334712/how-do-i-update-from-a-select-in-sql-server.
The temporary table can later be deleted in the same workflow.