If you ever need to transfer data between an Oracle instance and Microsoft SQL Server 2005, you’ll need to be aware of the differences between data types. In some instances this will require massaging the data so it’ll “fit” the schema at the destination, while maintaining the data integrity.

An interesting example of this is the date and time data types. In Oracle PL/SQL dates can take a value of between 1/1/4712 BC and 31/12/9999 AD. Prior to Oracle Server 8.0 (1997) the upper limit ended on 31/12/4712.  In Microsoft SQL Server 2005 the datetime data type range is between 1/1/1753 AD and 31/12/9999 AD. The smalldatetime as suggested by the name is even more restrictive with a range oof 1/1/1900 AD and 6/6/2079 AD. This is a huge discrepancy between the database servers, and therefore converting an Oracle date prior to 1/1/1753 to a char and then trying to convert back on the Microsoft SQL Server end to a datetime would throw an error. In terms of data integrity, decisions need to be made on whether the original record is dropped, partially imported, date stored in another field eg. a varchar, and so on.

Seeing the error of their ways Microsoft has beefed up the date and time data types with SQL Server 2008. The datetime data type still has the same restrictions so simply upgrading isn’t going to help. The improvements are in the new data type such as time, date, datetime2, and datetimeoffset. For example the datetime2 data type has a new lower boundary of 01/01/0001. These new data types align themselves with the SQL standard (according to Microsoft) and as such Microsoft recommends they are used instead of datetime in all new databases.