Yet Another Date Teaser

Denis Gobo — Thu, 10 Jan 2008 13:59:00 GMT

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?

SQL Teaser: Guess the output

Denis Gobo — Wed, 10 Oct 2007 14:46:00 GMT

What do you think will be the output?

DECLARE @d datetime
SET @d = '20071010'

SELECT DATEADD(yy, DATEDIFF(yy, 0, @d)+1, -1)