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
The expression 18/200 evaluates to 0. There is an implicit conversion to datetime; 1900-01-01 00:00:00.000. This is the month January, so 1.
The expression 200/18 evaluates to 11. This is converted to datetime; 1900-01-12 00:00:00.000. This is the month January, so 1.
DBArtisan downloaded and installed
Hi,
If we pass Integer values to Month() function, then it adds number of days with base date (1st Jan 1990).
for SELECT Month(18/200)
18/200 result would be 0, because of an integer. 0 days are added into base date, then our date would be same with base date (1st Jan 1990), now we get month of the date(1st Jan 1990) then it returns as 1 (b/z Jan is an first month).
for SELECT Month(100/18)
200/18 result would be 11, so 11 days are added into base date, then our date would be 12th Jan 1990, now we get month of the date (12th Jan 1990) then it returns as 1 (b/z Jan is an first month).
for ex. SELECT Month(31)
Now 31 days added with base date(1st Jan 1990), then our date would be 1st Feb 1990, now we get month of the date (1st Feb 1990) then it results would be 2.
Thanks,
Selvakumar S
MONTH(18/200) = MONTH(0) == MONTH(DATEADD(DAY, 0, ‘1900-01-01 00:00:00.000’)) = 1
MONTH(200/18) = MONTH(11) == MONTH(DATEADD(DAY, 11, ‘1900-01-01 00:00:00.000’)) = 1
Downloaded DBArtisan
MONTH ( date )
date
Is an expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value. The date argument can be an expression, column expression, user-defined variable, or string literal.
If date contains only a time part, the return value is 1, the base month
18/200 = 0.0.9 =time part
200/18 = 11.11=time part
it contain time part so it give 1
This is the solution i found to this puzzle.:-
The Month function, when parameterized with a integer / decimal gives the current month in integer according to the number of days in the current month.
Ex – Month(30) returns 1 since June contains 30 days . (Considering the current month as june) Date Range(0-30) => 1
Month(41) returns 2 since it would fall into the next month hence 2.(Date range 31-62 )=> 2
Month (75) would return as 3 since it would fall under 3rd month from the current month.
This would continue until 12 and then the counter would reset to 1.
The same applies to negative integers too. the previous month would be returning 12.
Ex Month(-1) => 12
Our Problem :-
Month(18/200) would return as 1 since 18/200 = 0.09 which is in the date range 0-30 (Considering June)
Also, Month (200/18) would also return as 1 since 200/18 = 11.11 which also is in the date range 0-30
Thanks
Manju
Thank you for the amazing response. There are around 280+ responses. I am going through each of the response today and hope to announce the winner by Monday.
Thank you all for participating. I have just sent a winners email with the subject line – Month Puzzle. Please reply within the next 7 days that you are willing to accept the gift card and I will send you a gift voucher via email.
Can you share the winners list in this blog?
Absolutely, I have just done it.
Hi Pinal, Thanks for the puzzle. I’m accepting the gift.
Thanks Gopi
Hi Pinal,
Can you also post the names of the Winner here as well. Also, can you also provide the correct answer to the puzzle?
Hi Vikas,
There are many correct answers. I will soon write a separate blog post about it.
Hi All,
Here is the winner of the contest.
Tom Harrocks
https://blog.sqlauthority.com/2015/05/20/sql-server-puzzle-with-month-function-win-usd-50-amazon-gift-card/#comment-1748470
Sanjay Monpara
https://blog.sqlauthority.com/2015/05/20/sql-server-puzzle-with-month-function-win-usd-50-amazon-gift-card/#comment-1744750
They should have received the email from me.
Congratulations!
I received my flipkart gift voucher!
Thank you very much :-)
Glad to hear that. Thanks for being active over the blog.
Was my answer wrong or it just came later?
The selection is random from all the valid answer.
Congratulations to Winners –
Here is the next quiz contest – https://blog.sqlauthority.com/2015/06/09/sql-server-puzzle-with-year-function/
If you have not won now – you can win in this next contest.
Hello Pinal,
I received my flipkart gift voucher!
Thank you for wonderful contest and gift :-)
If date contains only a time part, the return value is 1, the base month.