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
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
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?
Was my answer wrong or it just came later?
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.