bigM (4/2/2010)
When I insert the datatime row into a table as below, I get different result. Any idea?use tempdb
go
create table test1 (dat datetime)
go
insert into test1 values('1753-01-01')
select * from test1
RESULT:
1753-01-01 00:00:00.000
Here you specify a string constant, using the 'yyyy-mm-ddd' format. SQL Server will convert the string constant to a date. Although the 'yyyy-mm-dd' format is not guaranteed to be interpreted correctly in all cases (only 'yyyymmdd', 'yyyy-mm-ddThh:mm:ss' and 'yyyy-mm-ddThh:mm:ss.mmm' are guaranteed), I have not found cases where it is misinterpreted. (And with month and day both equal to 1, it would in this case not even make a difference). So the conversion results in the datetime value equivalent to January 1st, 1753.
insert into test1 values(1753-01-01)
select * from test1
RESULT:
1904-10-18 00:00:00.000
:unsure:
In this case, there are no quotes. SQL Server will interpret 1753-01-01 as an expression that consists of three integers and two subtraction symbols. It will compute the result, which is the integer value 1751. This then has to be stored in a datetime column, so implicit integer to datetime conversion kicks in. And that conversion rule is that the resulting datetime value is computed by adding the integer number of days to the base date of Jan 1st, 1900. The result, October 18th, 1904, is apparently exactly 1751 days after this base date.