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
Here,
month function takes date as input parameter.
The result of 18/200 and 200/18 will come to the “1900-01-01 00:00:00” that is default date of SQL Server.
From the result month function will return the “1” as month.
Thank you,
Because they are in first month.
Month function takes first 30(0-30) days as month 1, next 30(31-60) days as month 2 and so on….
For example Month(0)=1, Month(30)=2, Month(60)=3 … Month(360)=12
Here, 18/200 =0, 200/18 =11.
So both 18/200 and 200/18 falls under 30(first month).
So result is 1.
Correction: Month takes first 31 days(0-30) as Month 1 as January is first month and it has 31 days. Next 28 days(31-58) as Month 2 as February is 2nd month and it has 28 days this year and so on…..
So Month(0)=1, Month(30)=1, Month(31)=2, Month(58)=2, Month(59)=3…….
Hi Pinal,
The argument that you are passing to MONTH function is number. So SQL Server interprets as 1900-01-01.
So if you pass any integer to MONTH function, it will return 1 :-)
e.g. SELECT MONTH(1.5),MONTH(0), MONTH(10),MONTH(10/11)
Thanks,
pratik v. patel
18/200 = 0.09 – part time and 200/18 = 11.111 – part time.
If date contains only a time part, the return value is 1, the base month.
If it is correct answer click here to read it https://docs.microsoft.com/en-us/sql/t-sql/functions/month-transact-sql?view=sql-server-2017.
Contest 1 : For month function, it consider it as only time part of date is passed. So it returns only base month as 1. It is valid value so it is not giving the error.
If date contains only a month or year part, the return value is 1, the base month.
As BOL says:
“The following statement returns 1900, 1, 1.
The argument for date is the number 0. SQL Server interprets 0 as January 1, 1900.”
SELECT YEAR(0), MONTH(0), DAY(0);
https://docs.microsoft.com/en-us/sql/t-sql/functions/month-transact-sql?view=sql-server-2017
as soon as you pass an number value to month function, it’ll treat as day numbers, and it’ll add (if its a positive value) this number to the date “01/01/1900”
so, this expression SELECT MONTH(18/200) is the same as SELECT MONTH(0),
and the expression select MONTH(200/18) is the same as select month(11)
add 0 to day 01/01/1900 doesnt change the month (is still january) in the select function
the same happens if we add 11 to day 01/01/1900 (doesnt change the month (is still january))
But if you use an expression like SELECT MONTH(62/2), where sql evaluates to SELECT MONTH(31)..so we’re adding 31 days to 01/01/1900, just like this
select month(dateadd(dd,31,’1900-01-01′)) and this returns 2.
As 18/200 will return 0 and 200/18 will return 11. So query will be look like this
SELECT MONTH(18/200), MONTH(200/18) = SELECT MONTH(0), MONTH(11)
So whenever we pass the time part or any integer in the Month function it return the base month i.e. 1. So that’s why it showing 1,1
In Month function when we give number like 0 or 1 as input . It will add that many days in the base data 1 January 1900 and based on the new date it found it will return the month for that date.
e.g. if we give month (1) it will add 1 days to 1 January 1900 and return month for that date. In this case it’s 1.
if we give month (33) it will add 33 days to 1 January 1900 and new date will be (1900-02-03) . So month function will return 2 in this case.
Now in the given example .
1) when we divide (18/200) it will return 0 as int value and as per above detail it will give 1 as month.
2) when we divide (200/18), it will return 11 as int value. Now 11 will be added to the base date (1900-1-1) so new date will be (1900-1-11). Hence month function will return 1 in this case even.
Hello Pinal,
Here date function – Month is considering 1900-01-01 as base date.
for MONTH(18/200), 18/200 = 0 . so date would be 1900-01-01.Month would be January and result = 1.
for MONTH(200/18), 200/18 = 11. So date would be 1900-01-01 + 11 days = 1900-01-12.For this case also month is January.
Nishant
Both 18/200 and 200/18 returns integer values adds to January 1, which will not create an error and returns answer 1 because days added results in Jan1 and Jan12 are in Month 1 (Jan) itself.
The first expression is evaluated to 0, which sql server interprets as January 1,1900. And month function returns a integer value, so in this case it is January which is 1st month so u it returns 1.
The second expression is evaluated to 11.11 which sql server interprets as a time because if the date contains only time then sql server returns 1 that is a base month.
It is the same as this query
select MONTH(DATEADD(DAY,200/18,0)),MONTH(DATEADD(DAY,18/200,0))
200/18 days are added to the first date in sql server ie. 1900/01/01. the month function then executes on the date that is returned.
200/18 is 11. The default first date in sql server is 1900/01/01.
11 days are added to this date .
Hence both values are 1.
Answer contest No 1: If we convert 18/200 & 200/18 to datetime it gives “1900-01-01” and “1900-01-12” respectively. The month() function takes datetime value as argument. so in both case it returns 1 as the month is “Jan” in both case.
If we do SELECT cast(18/200 as datetime), cast(200/18 as datetime) then values returned are 1900-01-01 00:00:00.000 1900-01-12 00:00:00.000. They clearly belongs to 1st month. Given query is casting integer values in datetime and month function is returning month out of date casted
The result of the first division equals “0” and sql interprets it to January which means 1
the result of the second division equals 11 which does not exceed the number of days in one month –> 30, so it gives me 1 which means this count still in the range of one month
hope it is right.
The value is 1 because , the MONTH function treats any non valid date as “Jan 1, 1900”.
Thanks
Hello,
Month function needs date as parameter and hence for above case, it interprets given values as 1st and 12th Day of January 1900
You can check with executing below statement
SELECT CAST((18/200) AS DATETIME),CAST( (200/18) AS DATETIME)
Output
——————-
1900-01-01 00:00:00.000 ,1900-01-12 00:00:00.000
Hence the output is 1 as it’s first month
To verify it executed
SELECT MONTH(200/2) in this case SQL treats it as 100th Day of Janury 1900 ,which is in April and hence output is 4
Regards
Yudhishtir Bhole
This is because, MONTH function calculate the date first and then convert it to month.
E.g. MONTH(18/200): 18/200 will give you 0 and which results to first day of January. So the answer is 1.
MONTH(200/18): 18/200 will give 11 and which results to the 12th day of the January month. So the answer is 1.
When you do 200/18 or 18/200 it might take default 1900-01-12 00:00:00.000 and 1900-01-01. Both have month 1.