SQL SERVER – Puzzle with MONTH Function – Win USD 50 Amazon Gift Card

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?

How to Participate?

• Leave an answer for contest 1 in the comment section of the blog.
• 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)

SQL SERVER – Example of Performance Tuning for Advanced Users with DB Optimizer

• 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.

• Akhil Mahajan
June 2, 2015 4:43 pm

• Selvakumar S
June 2, 2015 7:21 pm

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

• Arvind Ravish
June 3, 2015 8:42 am

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

• Arvind Ravish
June 3, 2015 8:45 am

• 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

• Manjunath M S
June 3, 2015 2:44 pm

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.

• 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.

They should have received the email from me.

Congratulations!

• 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.