Upsert Destination: System.Exception: PipelineException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM

Problem

Sometimes you might get the following error while trying to load the Source data in SQL Server Table using Upsert Destination. 

Error: 0xC0047062 at Tickets, Upsert Destination (Insert, Update, Delete) [1602]: 
System.Exception: PipelineException: 
SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.. RowNumber=8
at ZappySys.PowerPack.Common.PipelineComponentBase.(Exception , ColumnInfoBase , Object , Int64 )

Possible Cause

In most cases, it happens due to you having SQL Table column(s) with the data type Datetime/SmallDateTime.

SQL Server DataTime data type accepts the date range between 1/1/1753 to 12/31/9999

And your API/Source data must have some out-of-the-range date data like this.

CreatedDate=0001-01-01T00:00:00

Here you can check the Date and time data types Range.

The Transact-SQL date and time data types are listed in the following table:

Data type	Format	Range	Accuracy	Storage size (bytes)	User-defined fractional second precision	Time zone offset
time	hh:mm:ss[.nnnnnnn]	00:00:00.0000000 through 23:59:59.9999999	100 nanoseconds	3 to 5	Yes	No
date	YYYY-MM-DD	0001-01-01 through 9999-12-31	1 day	3	No	No
smalldatetime	YYYY-MM-DD hh:mm:ss	1900-01-01 through 2079-06-06	1 minute	4	No	No
datetime	YYYY-MM-DD hh:mm:ss[.nnn]	1753-01-01 through 9999-12-31	0.00333 second	8	No	No
datetime2	YYYY-MM-DD hh:mm:ss[.nnnnnnn]	0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999	100 nanoseconds	6 to 8	Yes	No
datetimeoffset	YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm	0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 (in UTC)	100 nanoseconds	8 to 10	Yes	Yes

Possible Solutions

So as a solution, you need to change the data type to DateTime2 in the SQL Server Table instead of DateTime/SmallDateTime.

DateTime2[( n )] where n is fractional seconds precision from 0 to 7. 

datetime2(7) e.g. 2022-11-23 10:45:30.6782222
datetime2(4) e.g. 2022-11-23 10:45:30.6782
datetime2(2) e.g. 2022-11-23 10:45:30.67

Contact Us

if you have more questions feel free to contact us via Live chat or email at [e-mail]

https://zappysys.com/support/