Web Development

Ask something
Discussion Locked

SQL Server Dates Pre 1900

0 helpful
3 replies

by munrrob

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I am using DTS to transfer data from a Progress database to SQL Server 2000. Unfortunately, due to poor data collection, there are dates prior to 01/01/1900 in the progress database that cause the process to fail (I believe due to the fact that SQL does not support dates prior to 01/01/1900).
Any ideas how I can get around this?

3 replies

by tony hopkinson

SQL server does support dates before 1900 sort of but convert with string types does not. Astonishingly crap oversight, or are negative datetimes a feature I wonder.
If you insert -1 into a datetime field when you select from it you get 1899-12-31.
So you need to select the datetimes from progress as floating point values. Find out what progress's base date is and work out the offset (do that in progress it will be easier) e.g 1900-01-01 minus 1700-01-31
Do the arithmetic and whack the floating point result into the datatime field. You'll have to pump with a query of course.
DTS automatically does progress's convert date to varchar and then SQL server's convert varchar to date. That way it does not have to take into account differing native bases dates, or at least that was the theory anyway.

This discussion is closed to new replies.