datetime quirks
Datetime and smalldatetime data types can be confusing at times.
For example, consider the following select statement:
create table t1(c1 datetime)
go
insert t1 values('20010101')
go
select c1 + '1/1/3' from t1
go
If you expected '1/1/3' to represent january 1, 3AD, you're in for a little surprise.
This addition of a string to a datetime produces the following result:
2104-01-02 00:00:00.000
This happens because SQL Server treats '1/1/3' as '1/1/2003'. Then it calculates the difference in days between '1/1/2003' and '1/1/1900', and adds the numbers of days to the value stored in c1. This happens because January 1, 1900 is treated as the base date in SQL Server 2000 and SQL Server 2005.
