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.
  • 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. – Tibor Karaszi Jun 13 at 6:59
  • 2
    @TiborKaraszi - I betcha if you posted that as an answer, you might be able to farm some quick rep off it ;) – Peter Vandivier Jun 13 at 8:26
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.

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.