It has been a while since we ran the contest. I reached out to kind team of Embarcadero and they agreed to support the next contest. The contest has two steps and they are very simple. It took me a while to build contest, but it is a really fun one. I am very confident that once you try out the contest, you will love it.
Two Giveaways:
(Global) USD 50 Amazon Gift Card to 1 Individual
(India) INR 2500 Flipkart Gift Card to 1 Individual
Contest 1: T-SQL
Run following T-SQL script and observe the answer:
SELECT MONTH(18/200), MONTH(200/18)
When we look at the answer, it displays 1 as a answer to both of the expression. The question is why does above script return values as 1 instead of throwing an error or displaying any error?
Contest 2: Download and Install DBArtisan
This is the easy part of the contest. You just have to download and install DBArtisan. You can download DBArtisan from here.
How to Participate?
- Leave an answer for contest 1 in the comment section of the blog.
- Leave a comment with the same email address which you have used to download DBArtisan.
- The contest is open till June 2nd, 2015 Midnight PST.
- The winner will be announced on June 4nd, 2015.
- There will be two winners 1) Global 2)Â India.
- All the correct answer to the contest will be hidden till the winner is announced.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
309 Comments. Leave new
Month(0) through Month(30) returns 1 as Sql Server interprets Month(0) as Jan 1 1990. Month(31) becomes Feb 1 so forth..18/200 or 200/18 is under 30 hence in both cases it returns 1
18/200 returns a result of datatype integer and value 0 (due to integer division)
200/18 returns a result of datatype integer and value 11.
The MONTH function accepts a parameter of type datetime so the integer results from above both get implicitly cast to datetime.
When casting an integer to datetime the result is calculated by adding (or subtracting in the case of negative integers) the number of days to the base date of 1st Jan 1900. This is related to the physical storage where the datetime is stored as two integers concatenated together – One representing the date in this offset format and the other the time in approximately 3ms “ticks”,
Adding 0 days to the base date of course results in 1900-01-01. Adding 11 days results in 1900-01-12
Both of these dates are in January so the result of both function calls is 1.
Pinal, The reason these select statements are returning 1 is it’s the base month. Selecting 200/18 =11 I suspect the function interprets this as a time only part and returns the base month 1. Selecting 18/200 without converting for precision returns 0. This also returns 1 as the base month (1900,1,1).
Select MONTH(18/200),MONTH(200/18);
It must treats it as :
Select MONTH(0),MONTH(11);
=> SQL server interprets number 0 as January 1, 1900 and hence there is no error when we provides number argument for MONTH() function.
Hi Pinal,
Pleas find my below explanation.
DATETIME DataType supports Gregorian calendar.
Date ranges from January 1, 1753, through December 31, 9999
However the default Date is 1900-01-01 00:00:00
So when we say:
1900-01-01 its Day 0 (Verify: SELECT CAST(0.0 AS DATETIME) AS DATE)
1900-01-02 is Day-1 (Verify: SELECT CAST(1.0 AS DATETIME) AS DATE) and so on.
Now comes to the puzzle:
18/200 = 0.09
200/18 = 11.11
So when we add
1900-01-01 + 0.09 = 1900-01-01 02:09:36.000
1900-01-01 + 111.11 = 1900-01-12 02:38:23.997
So both the above dates fall in January month only
Therefore when we run
SELECT MONTH(18/200), MONTH(200/18)
it internally run as:
SELECT MONTH(‘1900-01-01 02:09:36.000’), MONTH(‘1900-01-12 02:38:23.997’)
Both returns result = 1, as month part of both the dates are January
Thank You
Because the MONTH function ha a date as parameter so it will convert the expressions to int and then add them as days to 1900-01-01:. So 18/200=0 and 200/18=16 (the result is int), which added to Jan 1st 1900 gives the same, respectively jan 17th, 1900, which are both in January hence month= 1.
You ca see from here:
select month(0), month(16), month(31)
first 2 will return 1 while the last will return 2 because 1+31=32, which would turn into Feb 1st, 1900.
Hi Pinal,
Plese find my explanation.
DATETIME DataType supports Gregorian calendar.
Date ranges from January 1, 1753, through December 31, 9999
However the default Date is 1900-01-01 00:00:00
So when we say:
1900-01-01 its Day 0 (Verify: SELECT CAST(0.0 AS DATETIME) AS DATE)
1900-01-02 is Day 1 (Verify: SELECT CAST(1.0 AS DATETIME) AS DATE) and so on.
Now comes to the puzzle:
18/200 = 0.09
200/18 = 11.11
So when we add
1900-01-01 + 0.09 = 1900-01-01 02:09:36.000
1900-01-01 + 111.11 = 1900-01-12 02:38:23.997
So both the above dates fall in January month only
Therefore when we run
SELECT MONTH(18/200), MONTH(200/18)
it internally run as:
SELECT MONTH(‘1900-01-01 02:09:36.000’), MONTH(‘1900-01-12 02:38:23.997’)
Both returns result = 1, as month part of both the dates are January
Thank You
In both above scenarios the answer is 1 why because Month() function accepts datetime parameter.
18/200=0 ,200/18=11
Month(0), Month(11) — here 0, 11 are consider like time.
The month function will contains only time in the above scenario.
The solution to this problem is found in both implied cast of division and implied cast of arguments within the Month function, combined with how Sql Sever represents date/datetime values.
1) Without an explicit cast, integer division results in integer results, therefore 18/200 has an implied value of 0(zero). try it… select 18/200 result is zero. Even with explicit casting of some sort, for example select cast(18 as float) / cast(200 as float) = .09, we still have a value < 1.
The inverse has similar behavior with the exception that implicit vs explicit casting results in 11 or 11 plus some fractional value.
2) With the casting of the division in mind, then we can explore how implicit casting of integer or float values are cast to dates/datetime values by Sql Server. If we select cast(0 as date) then we get an error because this is an explicit cast. On the other hand if we use the value in a context where we have implicit casting then we can see the implicit value of 0 as a date,
select DatePart(DAY, 0), DatePart(MONTH, 0), DatePart(YEAR, 0)
1,1,1900. So, time starts at 01-Jan-1900. The date portion is the integral portion of the number and the time is the fractional portion of the number (assuming it is cast to some type that has a fractional part).
So, putting this together we either have the integer number 0 or 11 for 18/200, 200/18 respectively. In either case we are implying 01-Jan-1900 or 11-Jan-1900 so Month() of either returns 1.
Congratulation!!!
When we use 0 in any one of YEAR, MONTH, DAY functions, the SQL server interprets 0 to 01-Jan-1900. So If we use 11, the SQL interprets as 12-Jan-1900 by adding 11 days into 01-Jan-1900. In SQL 18/200 results 0 and 200/18 does 11. Thus why the query(SELECT MONTH(18/200), MONTH(200/18)) returned the month of and instead of getting error.
Thank You!
Dave, I downloaded Artisan, logged into the forum, but I do not see a “comment section” on the forum, nor any section in which to post my answer.
Integer division.
First Query expresses 18/200 as zero, which is january 1st 1900. Second Query expresses as 11, which is january 12th 1900. Both queries run the month function on a date for january, hence the answers 1.
Also, I am trying to leaving my answer in the “Ask a Question” section, but it keeps saying zero words entered and “Enter some content”. It seems impossible to leave an answer.
Turns out, one has to “toggle editor” to HTML and then back to text, then it can count words.
Not good web programming on their part.
Because month () consider this input as time part. when we will provide time part only to Month(), it will always return 1 as base month
Month function calculates month on the basis of days as if we write Month(0) or Month(31) output would be 1. same if we write Month(59) it will give 2 as of Feb in 2015 is of 28 days.
Numeric value of 18/200 & 200/18 would be 0.09 & 11.11 so it would come under Jan that’s why result is 1 1 as number of days less than 31 would considered in Jan.
one correction here,:-)
Month(0)-means 1 day so it returns 1 month
So January is the first month and has 31 days. so Month(0) to Month(30) will give 1 .
and like wise February 2015 has 28 days, so Month(31) to Month(58) will give 2.
So answer is 1 and 1..
ENJOYYYYYYYY
Answer is 1 – first and 11.
It fundamentally comes down to the fact that the date is treated as a numeric offset of 1900-01-01 == 0. MONTH(18/200)=0.09 == Zero days difference to 1900-01-01 which is within January. MONTH(200/18)=11.111 == 11 days difference from 1900-01-01, which is within January.
My answer is: the answer is 1 and 1 meaning January. in both cases SQL server interprets the division as integer division (i.e. no decimals) and would return 0 in the first instance and 11 in the second. The Month function will perform an auto-cast of the integer result into a SmallDateTime variable. 0 as a date is 1/1/1900, which is in January. 11 as a date is 12/1/1900 (11 whole days past SQL Server’s zero date), which is also in January. The month function returns the month proportion of the date (January = 1) and therefore both items of the select return 1.
This is because SQL Server interprets 18/200 i.e 0 as ‘1-1-1900′ and 200/18 I.e 11 as ’12-01-1900’. Run the below query which might help understand the situation.
Select DAY(18/200) as [Day], MONTH(18/200) as [Month],YEAR (18/200) as [Year]
Select DAY(200/18) as [Day], MONTH(200/18) as [Month],YEAR (200/18) as [Year]
Good one Pinal..:)