SQL PUZZLE WITH Date Function

Question

Hi, I found the following query some where in the google . SELECT YEAR(35000/20) AS YEAR_VALUE----> Output = 1904 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.

Nitin Sontakke · Answer

I obviously do not have intention of spoiling anybody's joy of being amazed at it. At the same time, it is important to clarify how it is working. In SQL Server, internally, all dates are stored as numbers only as date is not an intrinsic data-type. So essentially 1 is considered to be 01-01-1900 and 2 is 02-01-1900 and so on. As such if you just query year(1), you will get 1900. Now just keep on going ahead like that. If you divide 1750 / 365 (days in year) you get 4 and now add default year 1900 to it and you get 1904. Just to get an idea, execute the following script: declare @dates integer = 350 while @dates begin select @dates += 1; select @dates [ integer ], day (@dates) [ day ], month (@dates) [ month ], year (@dates) [ year ] end Hope it clarifies.

Nitin Sontakke · Answer

Amir (and others who are here!), One more thing to remember is when you use Microsoft SQL Server Management Studio, by default, which means if you DO NOT apply any function, dates data types are shown in internationally neutral date format, which is mm-dd-yyyy. As a developer, your local / cultural date format should NEVER come to your mind. Formatting the date for the user is User Interface Layer business and let it do it.

Amir Fühl · Answer

In this example, 02-01-1900 is January 2nd or February 1st?