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:

SQL SERVER - Puzzle with MONTH Function - Win USD 50 Amazon Gift Card amazon-gift-cards

(Global) USD 50 Amazon Gift Card to 1 Individual

Solarwinds

(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)

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

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)

Solarwinds
Previous Post
SQL SERVER – Antivirus Exclusions Best Practices With SQL Server
Next Post
SQL SERVER – Adding Reference Data to Master Data Services – Notes from the Field #081

Related Posts

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.

    Reply
  • Akhil Mahajan
    June 2, 2015 4:43 pm

    DBArtisan downloaded and installed

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

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

    Reply
  • Arvind Ravish
    June 3, 2015 8:45 am

    Downloaded DBArtisan

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

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

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

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

    Reply
  • 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?

    Reply
  • Reply
  • Was my answer wrong or it just came later?

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

    Reply
  • Sanjay Monpara
    June 14, 2015 6:58 pm

    Hello Pinal,
    I received my flipkart gift voucher!

    Thank you for wonderful contest and gift :-)

    Reply
  • If date contains only a time part, the return value is 1, the base month.

    Reply

Leave a Reply

Menu