Loading

SQL PUZZLE WITH Date Function

SQL PUZZLE WITH Date Function

Sai Reddy

Sai Reddy

8y880

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.

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke8y ago
Accepted 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 < 750
  begin
    select @dates += 1;
    select @dates [integer], day(@dates) [day], month(@dates) [month], year(@dates) [year]
  end
Hope it clarifies.
Nitin Sontakke7y ago
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ühl7y ago
In this example, 02-01-1900 is January 2nd or February 1st?