Tuesday, August 21, 2007

MS SQL Server error on changing Date types

Error: The conversion from datetime data type to smalldatetime data type resulted in a smalldatetime overflow error.

This happens because the default value of the date field is set to something like '12/29/1899' or '12/30/1899'. This strange looking date has been included by MSFT in SQL Server as well as in Excel (also 1900 is included as leapyear as I found).

As I found on a website, 29 February 1900 does not exist in most countries. From 1 March 1900 both Excel and the Enterprise Manager are the same.

After changing these dates to valid dates, and running an update query such as

ALTER TABLE dbo.TableName ALTER COLUMN ColumnName smalldatetime

works fine.

No comments: