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://darkslategrey-bat-805937.hostingersite.com)
309 Comments. Leave new
Since a date is not provided, Month interprets the date as 1-1-1900. Thus, returns 1
it expects a datetime value for MONTH() and so SQL needs to convert the number to a datetime representative time since 1900-1-1. So month(1), month(11) etc are all =1 anyways
the month function is interpreting the resulting integers as day of the year, so the results, rounded as int,, are 0 and 11, returning 1 (Jan) . SELECT MONTH(364/1) returns 12 (Dec)
The result of 18/200 is .09 and result of 200/18 is 11.11. Both results are acceptable for datatype of “time”. The argument of Month Function (Month(date)) accepts value of datatype “time” for the argument (date)
date
Is an expression that can be resolved to a time
So in this case it resolves to time. Time datatype accepts fractional second precision. So .09 is accepted as hh:mm[:ss][.fractional seconds]. In this case 00:00[:00][.09]
The other result 11.11 is accepted as [ss].fractional seconds. In this case 00:00[:11][.11]
If the date argument contains only a time part, the return value is 1, the base month.
So we get the output of 1 for both expressions.
The result of 18/200 is .09 and result of 200/18 is 11.11. Both results are acceptable for datatype of “time”. The argument of Month Function (Month(date)) accepts value of datatype “time” for the argument (date)
date – Is an expression that can be resolved to a time
So in this case it resolves to time. Time datatype accepts fractional second precision. So .09 is accepted as hh:mm[:ss][.fractional seconds]. In this case 00:00[:00][.09]
The other result 11.11 is accepted as [ss].fractional seconds. In this case 00:00[:11][.11]
For the MONTH function, If the date argument contains only a time part, the return value is always 1, the base month. Both the result of 18/200 and 200/18 resolve to only time part, so we get the output of 1 for both expressions.
select CONVERT (datetime,18/200), CONVERT (datetime,200/18)
Result:1900-01-01 00:00:00.000 1900-01-12 00:00:00.000
Month(‘1900-01-01 00:00:00.000’) =1
Month(‘1900-01-12 00:00:00.000’)=1
MONTH(31) will return 2 which is Feb. Since both 18/200 and 200/18 are less than 31 MONTH function returns 1 for January.
Hi Pinal, the MONTH function is to calculate the month value for given number of days in the bracket. As 18/200 and 200/18 returns less than 31, both results are 1. Try this SELECT MONTH(65) gives 3 while SELECT MONTH(-65), MONTH(300) returns 10. If the value is negative it subtracts from 365 and returns the month number for the days of the year.
In “SELECT MONTH(18/200), MONTH(200/18)” statement, SQL Server converts 18/200 as 0 and 200/18 as 11 (can be verified by “SELECT 18/200, 200/18”). Hence, actual output will be of “SELECT MONTH(0), MONTH(11)”. Now, Month function is considering 0 value as “1900-01-01 00:00:00.000” and 11 value as “1900-01-12 00:00:00.000” (can be verified by “Select CONVERT(datetime,0), CONVERT(datetime,11)”). So, now in both the dates month is January, hence SQL server returns “1” & “1” for “SELECT MONTH(18/200), MONTH(200/18)” statement.
Answer for Contest 1: Its because SQL server interprets integer value as no of days in a month and it increments as the number goes up. For Ex:- 0-30 days would be considered as January (1), 31-58 as February(2),so on….
The result for 18/200 is zero, when the argument for date is the number 0 then SQL Server interprets 0 as January so for the first query SELECT MONTH(18/200) returns 1.
Also, when you pass only time part in the date argument then the return value is 1, the base month. so SELECT MONTH(200/18) returns 1
Hi Pinal,
I have got the solution and would like to share with you all. Please have a look on the below :
First of all I would like to tell the concept / logic on the basis of that it works and later on for substantiation we will take an example and lastly the said
problem.
Concepts/Logic :
As we know, Every date related functions like, Month , Day , Hours etc are working on the date only. and If inside those functions a proper date has not been supplied
then SQL Server instead of throwing error, takes it’s default date i.e. 1900-01-01. And starts working on that as per user requirement.For Ex : user wants to adds
something in the Day part or in month part , then value whatever has been provided into that particular functions, will gets added into default date that specifc
part.And as a result of that we are getting the result. Since month, Days are fixed for every year so it doesn’t make any differences with default date.
Illustration :
Here is taking an example in the same format as is given in the said problem:
Suppose , there is a statement – SELECT MONTH (200/2).
When we are executing this statement, it actually behaves like following statement :
SELECT MONTH(MM,DATEADD(DD,200/2,0)).
When this statement gets executed , it’s gives us 4 as a month. it happens because of – When we simply divide 200 by 2 we are getting – 100. And in the above SELECT
statement 100 is being added in the day part of default date i.e. 1900-01-01. After this addition, outcome of this statement would be – 1900 – 04- 11 and month will be
4.
If we are executing actual problem statement of the example then we will get – 4 as a month. Means both results are same.
That means here we can say that based on that concept / logic it’s working.
Actual Problem :
The given problem is : SELECT MONTH (200/18).
Hence , Trying to explain it on the basis of above example :
In the above ex we have divided 200 by 2 whereas here we have to divide by 18 instead of 2. Apart from that Rest of things are same.
If we are diving 200 by 18 then the outcome will be 11 and as per the above ex this value needs to be added in the day part of default date. After addtion we will have
1900-01-11 and month is 01 into that particular date.
In the problem statement we are getting same result. so this the reason why we are getting that.
Similary, we can find out the solution its second query.
So above is the solution of the said problem.
Hi sqlauthority blog,
SELECT MONTH(18/200), MONTH(200/18)
The reason behind the results of above query is,
while MONTH function taking the 18/200 and 200/18 values and casting to datetime format, It returns below results.
SELECT CAST(18/200 AS DATETIME) :Result: ‘1900-01-01 00:00:00.000’
SELECT CAST(200/18 AS DATETIME) :Result: ‘1900-01-12 00:00:00.000’
So, in the above result month values are 1. So, it returns 1 as value for
SELECT MONTH(18/200),MONTH(200/18)
SELECT MONTH(18/200), MONTH(200/18)
SELECT 18/200 AS FirstResult, 200/18 AS SecondResult
–Day 0 is 1900-01-01
SELECT CAST(18/200 AS DATETIME)
–Day 11 is 1900-01-12
SELECT CAST(200/18 AS DATETIME)
SELECT MONTH(CAST(11 AS DATETIME)),MONTH(CAST(0 AS DATETIME))
–OR
SELECT MONTH(CAST(18/200 AS DATETIME)),MONTH(CAST(200/18 AS DATETIME))
Answer for Contest 1: TSQL (SELECT MONTH(18/200), MONTH(200/18)).
The argument for MONTH() is datetime. In this case we are passing 18/200 and 200/18, which resolve to integers (0 and 11, respectively) since no decimal point was specified in the division operation. When converting from integer to datetime, 0 is interpreted as January 1, 1990. Each whole number is equivalent to another day added to January 1, 1900 (or subtracted if the number is negative). Hence, 11 converted to datetime is January 12, 1900.
Both January 1, 1900 and January 12, 1900 fall in the first month of the year, the result of
SELECT MONTH(18/200), MONTH(200/18)
is
1 1
:-)
MONTH function returns an integer that represents the month of the specified date.
If date contains only a time part then the return value is 1 to denote base month. Here 18/200 and 200/18 considered as a time part of date so its returning 1.
basically MONTH(X) will add X days to the lowest value of the date (for example smalldatetime it is 1 jan 1900) and return the month.
For Case 1- ‘Select MONTH(18/200)’ sql server first calculate the 18/200 and convert it to the integer which will become 0 and the add 0 days to the 1 jan 1900 then date become 1 jan 1900 and month function will return 1 for the jan month.
For Case 2- ‘Select MONTH(200/18) sql server first calculate the 200/18 and convert it to the integer which will become 11 and the add 11 days to the 1 jan 1900 then date become 12 jan 1900 and month function will return 1 for the jan month.
MONTH function in SQL server accept date as an 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 and returns INT.
When we when we write SELECT MONTH(18/200) SQL server take it as SELECT MONTH(0)
because whole number of 18/200 = 0.
Result of SELECT MONTH(0) is 1 because month function takes 0 to 30 as month 1 because January i.e first month of the year is always having 31 days.
Also MONTH function will take 31 to 58 as month 2 because Feb will have 28 days except leap years and so on month function takes 59 to 89 for march i.e month 3 for third month of the year is always having 31 days. and so on…
That is why SELECT MONTH(200/18) which is taken by SQL Server as SELECT MONTH(11)
because whole number of 200/18 = 11, returns 1
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.
DBArtisan downloaded and installed