I am having some issues inserting a DateTime variable into a MS SQL database using the Execute Query activity.
Everything works fine as long as I use a date between 1st January 1753 and 31st December 9999.
But there might be cases where I need to enter the date 1st January 0001 (0001-01-01) but then I get a SQLDateTime overload error.
The date column in the SQL table is of type DATE. I have read that the above date range applies in SQL for DATETIME whereas programatically the supported date range starts 1st January 0001. To support this DATETIME2 should be used in SQL.
But that does not make any difference. Also I have no problems inserting the date 0001-01-01 directly in the table.
Then I have tried converting the date to a string before using the Execute Query activity and then it accepts the date 0001-01-01 and inserts it into the database without problems.
Any idea of what I am doing wrong here? Is it some kind of check within the activity that prevents the date to be inserted as the activity might uses the old DATETIME?