Saturday, January 1, 2011

T-SQL Challenge

What will be the output of below T-SQL code:

CREATE TABLE #TestDate
(
   [ID] int IDENTITY(1,1)
   ,[FullDate] datetime DEFAULT (GETDATE()),
)
GO
INSERT INTO #TestDate VALUES
('01/07/2010'),('2010/07/01'),('07/01/2010')
GO
SELECT COUNT([FullDate]) FROM #TestDate
WHERE CAST([FullDate] as int) = 40358
GO
DROP TABLE #TestDate
GO

Answer this without cheating (without executing the Query). Here are the options:

A.   1
B.   2
C.   3
D.   Error
F.   None of the above


Correct Answer is B.
Reason: Dates 07/01/2010 and 2010/07/01 are inserted as 2010-07-01 00:00:00.000 in the database which is equal to 40358 of int type. So when the conversion into int and then taking ceiling of the values, these 2 records generate the same values. However, 01/07/2010 is saved as 2010-01-07 00:00:00.000 in the database which is equal to 40183 of type int.

No comments:

Post a Comment