SQL Server 2008 Date Teaser

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?