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 Pinal,
I tried to resolve this puzzle –
Puzzle –
SELECT MONTH(18/200), MONTH(200/18)
Explanation –
First Sql Server will try to evaluate below expression
Example –
SELECT 18/200 – Output is 0
SELECT 200/18 – Output is 11
Now expression would be like this
SELECT MONTH(0), MONTH(11)
Now Month function is expecting parameter as a datetime, function will try to convert 0 and 11 into datetime type
Here is outcome for the same –
SELECT convert(datetime, 0) – Output is [1900-01-01 00:00:00.000]
SELECT convert(datetime, 11) – Output is [1900-01-12 00:00:00.000]
Now Month function got as datetime type as expected it will try to pull month value from datetime parameter. Then output would be 1 and 1 for this puzzle.
Contetst1: The month function can accept a date offset, so passing 0 and 11 to it is not an error. I believe it interprets these as date offsets in seconds. Therefore it takes these to be date offsets in seconds from Jan 1 1900 so it adds 0 and 11 seconds respectively to Jan 1 1900, which means it is still January 1900, so the month function returns 1 for January.
Hi Pinal,
How are you..
I really enjoyed the puzzle. Its nice and interesting one.
Puzzle #1.
As we know, SQL server month function always returns an integer value within the range of 1-12 as month value except the case of null.
Month function syntax : MONTH ( date )
We are always passing date as expression in month function but if we are giving any integer value to the month function, SQL server automatically interprets integer value to date value and returning month value instead of throwing an error.
Let me clarify this,
select month (0) -> it will interprets as date value “1900-Jan-01” and will be returned value “1” as January month in the date.
Hence, If providing integer value ’31’ as expression then it will be returned “2” because date value should be considered as ‘1900-Feb-01’ after 31 days from “1900-Jan-01”.
Let me come to the puzzle.
why does select month (18/200), month (200/18) script return values as 1 instead of throwing an error or displaying any error?
SQL server dividing the numeric expression and passing an integer value to the month function.
select 18/200 –> result ‘0’ passing and month functions returns value 1. { SQL server Interprets value as ‘1900-01-01′ }
select 200/18 –> result ’11’ passing and month functions returns value 1. {SQL server Interprets value as ‘1900-01-12’ }
As I mentioned above, If we are providing NULL value to the month function as expression, It will returns NULL.
Thanks & Regards
Nikhildas Chandran
[removed phone and address]
SELECT MONTH(18/200), MONTH(200/18). This is equivalent to SELECT MONTH(0), MONTH(11).
Now, if there is an integer passed as a parameter, then the logic is as follows:
0 – 30 (31 days) Jan Return value as 1.
31-58 (28 days) Feb Return value as 2. and so on.
Hence Month (0) and Month (11) would return 1 (January) by the above logic.
MONTH function returns the month from 1 to 12 given a date value. but it is (18/200) and (200/18) case sql server inter-prate it as jan 1 1900 and returns value 1 in both SELECT cases.
month(@date)
@Date –> Is an expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value
SELECT 18/200 –> 1 as it is converted to an integer
SELECT CONVERT(SMALLDATETIME,1) –> 1900-01-02
SELECT MONTH( CONVERT(smalldatetime,1)) OR
SELECT MONTH( ‘1900-01-02’) OR
SELECT MONTH( 18/200)
–> month is january
SELECT 200/18 –> 11 as it is converted to an integer
SELECT CONVERT(SMALLDATETIME,1) –> 1900-01-12
SELECT MONTH( CONVERT(smalldatetime,11)) OR
SELECT MONTH( ‘1900-01-12’) OR
SELECT MONTH(200/18)
–> month is January
Regards
Benjamin
Answer for Month quiz:
Month() function in SQL accepts date as parameter.
Here in above quiz, we are passing 18/200 and 200/18 which is treated as an integer division and which returns 0 and 11 respectively. Month(0) and month(11) returns 1.
Thanks
Because both divination returns value below 30. it is considering return value as day input.
hence it is returning 1st month for both condition.
Thanks :)
SELECT MONTH(18/200)
Explanation
SELECT MONTH(18/200)
— Output 0
SELECT MONTH(0)
— Output 1
SELECT MONTH(200/18)
Explanation
SELECT (200/18)
— Output 11
SELECT MONTH(11)
— Output 1
Hello It is bug, this function (month) accept only string of date value then why it has not throwing bug.
It is SQL server Bug..!
This was the actual input SELECT Month(0.09), Month(11.11)
Month(0.09) returns 1 because when we pass a value like 1 to 31 in Month function means it will return 1 since it is January month
Example :
January 1- 31, February has 28 days so 32 – 59 and March has 31 days so 60 – 90
If we pass (32 to 59) in Month function it will return 2 and it indicates February Month
If we pass ( 60 to 90) in Month function it will return 3 and it indicates March month and so on
Answer : Since the input was SELECT Month(0.09), Month(11.11) and it falls between range 1 to 31 it returns the output as January Month so value is 1
The “MONTH” function in this example actually passes the value and the SQL takes that as the Nth DAY of the YEAR.
For instance:
When we pass a value 0 to 31. It consider that as 1st month which is January.( ie ) SELECT MONTH (5) => 1
SELECT MONTH(90) => 3 ( 90th day of the year is from March month, so it results 3 )
In other words, it always results the MONTH value ( 1 – 12) with the day count.
In given example, 18/200 = > 0.09 and 200/18 => 11.11
Both less than 31 which indicates 1st month of the YEAR January.
SQL calculates the 18/200 and 200/18 into a single value, and interprets it as a time value; the default of a time value using the month function is 1 (base month).
Both of the month example would return 1 because:
18/200 = 0 SQL interprets 0 as 1/1/1900. The month would be 1.
200/18 = 11 SQL Interprets 11 as 01/01/1911. The month would be 1.
I think this is correct. I also downloaded the DBARtisan. I manage SQL and PostgreSQL databases, so I hope this works with both.
Correction to my last answer.
I stated that SQL interepts 11 as 01/01/1911. This is incorrect. The correct answer for month(11) would be 1/12/1900..of which the month would be 1.
Both of the month example would return 1 because:
18/200 = 0 SQL interprets 0 as 1/1/1900. The month would be 1.
200/18 = 11 SQL Interprets 11 as 01/12/1900. The month would be 1.
I think this is correct. I also downloaded the DBARtisan. I manage SQL and PostgreSQL databases, so I hope this works with both.
SQL server performs implicit data type conversion behind the scene. Here is the conversion steps:
18/200 = 0
cast(0 as datetime) = ‘1900-01-01’
month(‘1900-01-01’) = 1
Similar for 200/18:
200/18 = 11
cast(11 as datetime) = ‘1900-01-12’
month(‘1900-01-12’) = 1
Not sure if this is where one attempts to answer. The reason begins to make more sense if you expand the query as follows:
SELECT 18/200 AS [18/200],
200/18 AS [200/18],
MONTH(18/200) AS [MONTH(18/200)],
MONTH(200/18) AS [MONTH(200/18)],
MONTH(0) AS [MONTH(0)],
MONTH(11) AS [MONTH(11)],
CONVERT(datetime, 0) AS [CONVERT(datetime, 0)],
CONVERT(datetime, 11) AS [CONVERT(datetime, 11)],
CONVERT(datetime, 300/10) AS [CONVERT(datetime, 300/10)],
MONTH(300/10) AS [MONTH(300/10)],
MONTH(310/10) AS [MONTH(310/10)]
Because 18/200 will divide as integers, the SQL default, the answer will be zero (0).
Likewise, the answer for 200/18 will be 11.
SQL dates are also counted by whole numbers per day (with fractions being the percentage of hours). Since SQL starts from 1900-01-01, the first answer zero is going to convert to that date. The month for 1900-01-01 is month 1.
Since 200/18 is 11, that is the 12th day (using base zero), which is 1900-01-12, which is still in the frist month (January), hence the month for 1900-01-12 is also month 1.
If you look at the last three fields in the SELECT (above), you’ll see that even MONTH(300/10), which is MONTH(30), which is MONTH(1900-01-31) is still month 1.
However, in the last field, MONTH(310/10), which is MONTH(1900-02-01) is now month 2.
Simply put, it is the month of the date beginning at 1900-01-01 + the integer days derived from the example divisions you gave.
Contest 1…
There are several reasons this happens:
1) The integer expression 18/200 and 200/18 are evaluated as their results, 0 and 11 (note 0 and not 0.09 as these are implicitly integers so no decimal output)
2) The default treatment of the integer datatype conversion for the MONTH function is to DATETIME. This is a integer based datatype with a base of 0 being equal to 1900-01-01. Therefore the MONTH 0 = 1900-01-01 = 1
3) 11 Translates to the 11th day after 1900-01-01 during the internal conversion to DATETIME = 1900-01-12, whcih is still a MONTH = 1
4) An explicit way to write these would be:
SELECT MONTH(CONVERT(DATETIME,DATEADD(DAY,0,’19000101′)))
SELECT MONTH(CONVERT(DATETIME,DATEADD(DAY,11,’19000101′)))
Dates and times are always stored as two separate components: a date component and a time component. the data is stored as two 4-byte values, the
first (for the date) being the number of days before or after the base date of January 1, 1900,
and the second (for the time) being the number of clock ticks after midnight, with each tick
representing 3.33 milliseconds, or 1/300 of a second.
When you assigning 0 day to datetime sql server start date from January 1, 1900 ex.: SELECT CONVERT(DATETIME, 0)
18/200 =
SELECT CONVERT(DATETIME, 18/200) it is start from 1900-01-01
SELECT MONTH(CONVERT(DATETIME, 18/200)) month is 1
200/18 =
SELECT CONVERT(DATETIME, 200/18), date is : 1900-01-12 00:00:00.000
SELECT MONTH(CONVERT(DATETIME, 200/18)) : month is 1
Date/time are floating point numbers
18/200 evaluates to .09 which is equivalent to Jan 1 1900 02:09 who’s month would be 1
200/18 evaluates to 11.11111… which is equivalent to Jan 11th 1900 02:40 who’s month would still be 1