It has been a while since my last teaser but here we go
What do you think the following returns?
SELECT
CONVERT(datetime,'1/1/1') -CONVERT(datetime,1) + CONVERT(datetime,0)
How about this on SQL Server 2008
SELECT
CONVERT(datetime2,'1/1/1'),CONVERT(datetime2,'01/01/01'),CONVERT(datetime2,'0001/01/01')
Now run this on SQL Server 2008
SELECT
ISDATE
('1/1/1'),
ISDATE
('01/01/01'),
ISDATE
('001/01/01'),
ISDATE
('0001/01/01')
Now just for fun run these 4 on SQL Server 2008, one of them will fail so run them one by one
SELECT
CONVERT(datetime2,'1/1/1')
SELECT
CONVERT(datetime2,'01/01/01')
SELECT
CONVERT(datetime2,'001/01/01')
SELECT
CONVERT(datetime2,'0001/01/01')
Compare the isdate output to the select statement, see the inconsistency?