0

I am syncing from PostgreSql to MSSQL using SymmetricDS. Some tables of the source database have timestamp field set too '0001-01-01 00:00:00.0' and it could not be able to insert to destination(SQL Server) database.

Is there anyway to work around this ?

AcknowledgeService - The outgoing batch 001-987 failed: Parameter arg '0001-01-01 00:00:00.0' type: TIMESTAMP caused exception: Only dates between January 1, 1753 and December 31, 9999 are accepted.
Danis Lam
  • 1

2 Answers2

1

Instead of using the (old) datetime datatype in SQL Server, you can use the (newer and better) datetime2. Among other things, it accepts earlier dates, back to year 1.

A tip is to specify how many digits you was for the second fractions. If you don't do it, you get 7 (as in 12:23:45.2314534). If you can get by with less, then specify for instance datetime2(0) and you get zero decimals. Or whatever you want/need.

Tibor Karaszi
  • 18,269
  • 2
  • 17
  • 30
0

Pseudocode:

set destinationValue = case when sourceValue = '0001-01-01 00:00:00.0' then null else sourceValue end

In English:

Can you check the value during the export/import? If so, then test for the case where the value is '0001-01-01 00:00:00.0' and in that case, set the destination value as null instead. When the above is not the case, then migrate the data as before.

If you want to be pedantic, make your test case when sourceValue < '1753-01-01'

If your destination does not accept nulls, then you need to decide how you want to represent those dates. One option is to set them to the value '1753-01-01 00:00:00' instead of null. Whether that works depends on how your applications are configured to consume the data in the destination.

youcantryreachingme
  • 1,655
  • 3
  • 21
  • 36