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)