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
The Month function takes datetime as parameter. When 18/200 is casted to datetime, it becomes 01-01-1900 and when 200/18 is casted to datetime, it becomes 12-01-1900. So, Month function returns 1 as both the date is of 1st month
Month() function here contains expression which can be converted in to time, date, smalldatetime, datetime, datetime2, or datetimeoffset and return type will be int always so in this case it is returning 1 because here date contains only time part so it will return 1 only the value.
Month(18/200)=Month(0.09) date contains here only time part so return value is 1
Month(200/18)=Month(11.111111) here also date contains only time part so return value is 1.
Any integer supplied to MONTH() is considered as a “day number” of an year (any year). Below are the examples I’ve used.
The Answer to your question is:- Since the result of both the values ( “18/200” & “200/18”) is >= 0 and < 31 it is still showing the month of January.
— Example
SELECT MONTH(GETDATE()) AS [MONTH(GETDATE())],MONTH(18)AS[MONTH(18)] ,MONTH(200)AS [MONTH(200)],MONTH(364.99)AS [MONTH(364.99)],MONTH(365)AS [MONTH(365)],MONTH(369)AS [MONTH(369)],
MONTH(-1)AS [MONTH(-1)],MONTH(-365)AS [MONTH(-365)],MONTH(-364.99)AS [MONTH(-364.99)],MONTH(-364)AS [MONTH(-364)]
SELECT 'Same as above, now with division of integers' AS [Description]
SELECT 18/200 AS [18/200], MONTH(18/200) AS [MONTH(18/200)],200/18AS [200/18], MONTH(200/18) AS [MONTH(200/18)],2/365AS [2/365],MONTH(2/365)AS [MONTH(2/365)],365/2AS[365/2],MONTH(365/2)AS[MONTH(365/2)]–, getdate(),MONTH(18/40)AS[MONTH(18/40)],
The query uses the MONTH() function, which relies on seeing a datetime value, and it will implicitly convert a supplied number. The results of the query are both 1, and as this might not be what you expect, you also have to take into account that as there are no decimal points specified, SQL Server will assume these are integer values, and with both parts of the fraction being integers, do integer division. The net result is that when you see those integer values as dates, the resulting MONTH() function values make sense.
Set SQL Server Management Studio to use “Results to Text”, and run the following to illustrate what’s happening:
SELECT MONTH(18/200), MONTH(200/18)
SELECT MONTH(18/200), MONTH(200/18), 18/200, 200/18
DECLARE @DT1 AS datetime, @DT2 AS datetime;
SELECT @DT1 = 18/200, @DT2 = 200/18;
PRINT @DT1;
PRINT @DT2;
This should help you see the intermediate results, as well as the original query result.
Very easy the answer, SELECT MONTH(18/200), MONTH(200/18) returns 1,1 because if don´t indicate the expresion with ” for date, the function take the sentence like a number, and the number is taken like a day of the year, example: 1 to 30 month=1, 31 to 58 month=2 etc
complementing the answer, 18/200 = 0 for Select in T-SQL for default evaluation, and the result of the sentence month(0)=1, en the case of 200/18 =11, the day 11 is in the 1 month, then the result of the sentence month(11)=1
SELECT MONTH(18/200), MONTH(200/18), the argument should be in date format to get the exact result. if it is not in date format and it is a numeric value then the result will be base value 1.
Hi Pinal,
There is two month function in your Question, so let me try to give answer one by one.
1. SELECT MONTH(18/200)
-> In this month function 18/200 yields to 0 (zero). and if parameter for date part is the number 0 then SQL Server interprets it as January 1, 1900 and it will return 1 as Month January.
2. SELECT MONTH(200/18)
-> In this month function 200/18 yields to 11 and if you pass 11 in Month function it will consider it as Time part. If you pass any time part the return value is 1, the base month.
Thanks
Sorry I am just forget to mention my email address for contest 2, Its [email address]
DateTime values are stored in SQL Server as decimal values that represent the number of days from 1/1/1900 and the decimal portion representing the time portion (as a percentage of 24 hours.) Understanding this explains why 1 is being returned for both values. 18/200 is being evaluated as a division by SQL Server and 18/200 = 0 when rounded to the same precision as the numerator and denominator. Zero equates to the date time of 1/1/1900, which is in the month of January. This is why MONTH(18/200) returns 1.
For the MONTH(200/18), it’s basically the same issue, except that the value being returned by the fraction is the whole number of 11. 11 corresponds to 1/12/1900, which is also in the month of January, so the value returned is also 1.
You can verify this by running this script:
DECLARE @dt DATETIME = 18/200;
SELECT @dt, MONTH(@dt);
SET @dt = 200/18;
SELECT @dt, MONTH(@dt);
If you set @dt to 33, MONTH(@dt) will return 2 for February.
-Eric Isaacs
MONTH function accepts INT values.That is why it does not throw error.
1)For the given value:MONTH(18/200),18/200=0,so MONTH(0) when we execute,it will give a value of 1 which is January,because parameter value acts as the number of days in the month staring from the month of January(Base Year (1/1/1900)).
And in the case of MONTH(200/18),200/18 will be 11.so it will 11th day of the month which will again be January.
So,now if we execute MONTH(31),it will give 2 which will be February which is the second month of the year.
So,0-30 will be January and 31 to 59 will be February and so on.
Downloaded DBArtisan now. Interesting tool
The month() function with a number passed is referencing the numbers of days after 1/1/1900 with January 1, 1900 being 0. Both expressions evaluate to less than 31, which would be February 1, 1900 thus the month of January, or 1, is returned.
18/200 = 0.09
200/18 = 11
i think month function considers 0.09 and 11 as time
so If date contains only a time part, the return value is 1, the base month.
Hi Pinal,
As we know that MONTH function accept date argument.
When I ran above T-SQL Script.
SELECT MONTH(18/200), MONTH(200/18).
It gave me 1 as a result set. The reasean behind that SQL Server internally accept argument as 0 for above script. As we know that If MONTH Function get 0 as argument then SQL Server internally interprets 0 as 1/Jan/1900 and it returns tha anser as first month of the year means 1.
SQL Month() function converts the date given into the function into day. The same has been calculated from the first day and decides which month it is. It doesn’t take what is the month given in the date. Hence Month(1) referred as first day and it is calculated as month 1. 18/200 is zero. Zero is considered as first month. and 200/18 is ’11’ as it is less than 30 it is considered as 1st month.
Interesting part is the first month has only 30 days and if you give Month(31), it comes as 2 (February ) and not 1( January). February has 28 days, rest all follow the same as our gregorian calendar. The same can be tested with DATEPART() and DATENAME(). Next year January has 31 days. If you give 395 it comes as January and not feb.
SELECT 18/200, 200/18 , CAST(18/200 AS DATETIME),CAST(200/18 AS DATETIME)
output of above query: 0, 11, 1900-01-01 00:00:00.000, 1900-01-12 00:00:00.000
After casting, we got 1900-01-01 00:00:00.000, 1900-01-12 00:00:00.000.
By looking the output, our input consider as day (1 to 31 days as per number of day in a Month).
Here I can say Month function does the similar job. 1st cast then read month number (0 to 11)
So no concept of exception, It just return month number based on input.
Hi, my answer would be for the first column of the script 18/200 = 0. The month function of the sql server interprets 0 as January 1 1900 so the return value is 1 which is equal to January month. for the second column of the script 200/18 = 11, the month function accepts a parameter with data type date. a date has two parts, date and a time. If date contains only a time part, the return value is 1. so in this case 11 is considered a time that is why the return value is 1.
There are a few things happening here.
1. Because the literals 18 and 200 are both specified as integers SQL Server performs integer arithmetic when doing the division operations. So although 18/200 is 0.09 on your calculator, as an integer calculation it is 0. And although 200/18 is 11.1 recurring on your calculator, as an integer calculation it is 11.
2. As far as SQL Server is concerned time began at midnight on January 1st, 1900.
3. When the MONTH function is performed on a number SQL Server implicitly casts it to a datetime. The integer portion is treated as days since January 1st, 1900 and the decimal portion is treated as the time within the 24-hour day.
Putting this all together the first result returns the month on January 1st, 1900 (i.e. 1) and the second result returns the month 11 days later on January 12th, 1900 (i.e. still 1). You can see this by running the following T-SQL:
SELECT 18/200 AS [18/200], 200/18 AS [200/18], CAST(18/200 AS datetime) AS [DATETIME(18/200)], CAST(200/18 AS datetime) AS [DATETIME(200/18)], MONTH(18/200) AS [MONTH(18/200)], MONTH(200/18) AS [MONTH(200/18)]
it is referring system date as 1999
Month takes a date parameter and return its month value in integer form if it recognize parameter value a valid date. if not its return always base month value which is 1. so in given contest both parameter values are not valid dates so month returning value 1 not the exception.