SQL SERVER – Puzzle – How Does YEAR Function Work?

There are many different datetime related functions such as DAY, MONTH, YEAR, DATEDIFF, etc available in SQL Server. For example, YEAR function can be used to extract year value from a date.

Let me show you a simple example

DECLARE @TRANSACTION_DATE DATETIME
SET @TRANSACTION_DATE='2016-10-19 15:20:30'
SELECT YEAR(@TRANSACTION_DATE) AS TRANSACTION_YEAR

When you run above script, it gives us following resultset.

Puzzle – Year Function

Now let us see an exciting mystery which involves Year Function.

Now execute the following SELECT statement

SELECT YEAR(35000/20) AS YEAR_VALUE

When you ran the above script, it gives us a result as the year 1904.

This is indeed a strange result. As you can see that 35000/20 is not a valid date value. The matter of the fact 35000/20 is results in value 1750.

Puzzle: How does SQL Server consider this as a valid date and return year value as 1904?

Please leave your answers in the comment sections. I will be publishing all the valid answers next week same time. I have so far posted quite a many puzzles, I believe this one is the most challenging puzzle.

I suggest you share this with your friends who know SQL and see if they can solve this or not. I promise you that the answer to this puzzle is straightforward, once you know the trick behind it.

Reference: Pinal Dave (https://blog.sqlauthority.com)