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
This have returned 1 as the number after dividing (18/200) is 0 and (200/18) is 11 and SQL server treats these values as time and returns as 1 the base month. This is by design for month function as SQL server will return 1 if date contains only a time part, the return value is 1, the base month.
Both (18/200) and (18/200) result in 0.
To evaluate MONTH function and avoid type clash , the value converted as datetime which
is “1900-01-01 00:00:00.000” , and thus MONTH evaluates to 1 in both the case.
Script SELECT MONTH(18/200), MONTH(200/18) contains hour/day or day/hour that is why it’s giving base value of 1.
SELECT MONTH(18/200), MONTH(200/18) contains part hour/day or day/hour that is why it’s giving value of 1 the base month.
on casting 18/200 & 200/18 as datetime is equivalent to 1900-01-01 00:00:00.000 & 1900-01-12 00:00:00.000 respectively. The Month portion of these values are 1. So, no exception is thrown/expected.
Both (18/200) and (18/200) result in 0.
To evaluate MONTH function and avoid type clash , the value converted as datetime which
is “1900-01-01 00:00:00.000″ , and thus MONTH evaluates to 1 in both the case.
During the conversion, the integer part is taken as number of days and decimal part for hours. In this example as the value 0 , its evaluated to “1900-01-01 00:00:00.000″ and month function returns 1
SQL server interprets as data time, so it converts the float values to data time and picks the month in it. select MONTH(convert(datetime, CONVERT(float,(18/200)))), MONTH (convert(datetime, CONVERT(float,(200/18))))
Date 1: 1900-01-01 00:00:00.000
Date 2 : 1900-01-12 00:00:00.000
So the month would be 1 for both
The default for an invalid DATETIME is “1900-01-01”, the default for NUMBERS and DATES is to add DAYS. So the answer will be “1900-01-01” + whatever the resulting number is in days. Then it takes the MONTH from that result. In this case SELECT MONTH(200/18), CAST((200/18) AS DATETIME) Is one day + “1900-01-01” which is still the month of January.
The input value MONTH takes is date. The valid return value from the MONTH function is from 1 to 12. Since the input passed to MONTH function is number and not date, so the invalid input, MONTH function returns the value as 1.
This can be proved by using SELECT MONTH(10). This will still return 1.
Email address: [email address removed]
18/200 and 200/18 are first converted to pure numeric then to DATETIME, as this query shows:
SELECT CAST (18/200 AS DATETIME), CAST (200/18 AS DATETIME);
1900-01-01 00:00:00.000 1900-01-12 00:00:00.000
Because the DATETIME data type, behind the scenes, is a long integer, with zero beginning at 12:00 AM morning of January 1st, 1900, the MONTH function converts these numbers to integers and then to DATETIME and then to Month (which for such small integers is always in January, 1900.
Any Number with month function with any number will convert in integer and it will give result 1.
SELECT MONTH(18/200), MONTH(200/18)
returns 1, 1 is because 18/200 and 200/18 are first converted to pure numeric then to DATETIME, as this query shows:
SELECT CAST (18/200 AS DATETIME), CAST (200/18 AS DATETIME);
1900-01-01 00:00:00.000 1900-01-12 00:00:00.000
Because the DATETIME data type, behind the scenes, is a long integer, with zero beginning at 12:00 AM morning of January 1st, 1900, the MONTH function converts these numbers to integers and then to DATETIME and then to Month (which for such small integers is always in January, 1900.
18/200 gives 0 as integer return and or 200/18 gives 11. As the returns are not in year format, function is considering it time and for time part, the return value is 1, the base month.
SELECT MONTH(18/200), MONTH(200/18)
returns 1, 1 is because 18/200 and 200/18 are first converted to pure numeric then to DATETIME, as this query shows:
SELECT CAST (18/200 AS DATETIME), CAST (200/18 AS DATETIME);
1900-01-01 00:00:00.000 1900-01-12 00:00:00.000
Because the DATETIME data type, behind the scenes, is a long integer, with zero beginning at 12:00 AM morning of January 1st, 1900, the MONTH function converts these numbers to integers and then to DATETIME and then to Month (which for such small integers is always in January, 1900.
The first part of query i.e. MONTH(18/200) is giving 1 because 18/200 will give us 0 and then the SQL Server will interprets that as 01-Jan-1900. The second part of the query is also giving 1 because 200/18 yields us 11 so it interprets 12-Jan-1900 as argument.
SELECT MONTH(18/200), MONTH(200/18)
–(No column name) (No column name)
–1 1
SELECT 18/200– Result: 0
— SQL Server interprets 0 as January 1, 1900.
SELECT CAST(18/200 as datetime)–Result: 1900-01-01 00:00:00.000
SELECT CAST(0 as datetime)–Result: 1900-01-01 00:00:00.000
— The month of 1900-01-01 00:00:00.000 is 1
SELECT 200/18–Result: 11
–SQL Server interprets 0 as January 12, 1900.
SELECT CAST(200/18 as datetime)–Result: 1900-01-12 00:00:00.000
SELECT CAST(11 as datetime)–Result: 1900-01-12 00:00:00.000
— The month of 1900-01-12 00:00:00.000 is 1
Answer for Both 1 and 2.
SQL server evaluates the expressions 18/200 and 200/18. As both give only fractions, it is assumed that it is only the time part of the date.
It does not contain the month part and hence it returns the base value of 1..
Contest 1: SELECT MONTH(18/200), MONTH(200/18)
Explanation :
MONTH() function expects a parameter with DATETIME datatype.
In the below contest, parameter 18/200 (=0) and 200/18 (=11) are implicitly converted to DATETIME and inner result is ‘1900-01-12 00:00:00.000’, so OUTPUT for both the expressions is 1.
SELECT MONTH(‘1900-01-12 00:00:00.000’), MONTH(‘1900-01-12 00:00:00.000’), so output is 1 1.
both the scripts are returning 1 as it is treating it as only the time part and hence returning the value 1 which is the base month. So it will always return 1 value for it.
MONTH can take time, date, smalldatetime, datetime, datetime2, or datetimeoffset value
Our above script is treating (18/200) and (200/18) as time value
parameter in MONTH() function is not supplied in ’18/200′ (single quote).. so it is not throwing any error and considering it as numeric value. its doing mathematical division on parameter value and because both result having decimal value, its giving 1 as answer . month can not be in decimal so 1 as answer
MONTH() returns an integer(1-12) that represents month. Argument for MONTH() can be an expression column expression, user-defined variable, or string literal that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value. If the argument contains only time part then MONTH() will return the 1 as the base month. In above case 18/200 = 0 and 200/18 = 11 is considered as time and hence it returns 1.