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!
For contest 1
When you pass an int parameter into a MONTH function, it represents the number of days that has been passed since January 1st 1900, so, in the two cases you passed 0.09 and 11, you return the Month value for January 1st 1900 and January 12th 1900
Month funciton returns int values.Month(18/200) returns 1 because 18/200 returns 0 ..means 0 days it returns base month 1.Any interger values in month function takes as days.so it returns based on days.days count returns month values.
Hello sir ,
This is Mohan..
well math functions converts integer value to date format and calculates the month.in this scenario 18/200 results to zero and it converts to zero date which can be 01/01/1900 which is default start date in sql.so this date has 1st month so it results to 1.for 200/18 results 11 which is 11th jan of 1900 again it is 1st month so it results 1.
Hope this can be the reason for the results.
Thanks
Expression : MONTH(Date)
As per above parameter it perform division and consider Result as Number of Days (Consider as time part not Date), than it convert into Month.
Example :
SELECT MONTH(18/200), MONTH(200/18), MONTH(58)
18/200 = 0 Days > Return 1 (base month)
200/18 = 11 Days > Return 1 (base month)
58 Days > Return 2
Conclusion : If date contains only a time part, the return value is 1, the base month.
Because Default value set in T-sql Command 1900-01-01 where 1900= year,01=month,and 01=day so what ever values you set instead of (200/18) you will get only answer 1
This is because you have only mentioned the time and hence the return value is 1
this result is, because sql interprets the integers resultant from 18/200 (0) and 200/18 (11) as dates 1900-01-01 and 1900-01-12, thats why the result of SELECT MONTH(1900-01-01), MONTH(1900-01-12) is 1 in both cases. https://msdn.microsoft.com/en-us/ reference this
SQL Server interprets 0 as January 1, 1900.
when we write MONTH(18/200) 18/200 returns 0 so it means month(0) sql server interprets 0 as january month i.e. 1.
when we write mo0nth(200/18) 200/18 returns 11 so it means month(11) it means month(11days) it will returns january month i.e.1
From the below mentioned T-SQL script problem, I figured out the way of deriving “1” and it as follows:
Question: SELECT MONTH (18/200), MONTH (200/18) = 1
Solution:
Total no. of days in a year = 365 days
I have listed all the months and days of a particular year in the below mentioned format. Based on serial
order of months, I have assumed return values i.e.., For Jan =1 and Feb =2 and so on….
Days In
Days In Year
Month
(Range)
Return
Values
Month
Jan 31 0-30 1
FEB 28 31-58 2
Mar 31 59-89 3
APR 30 90-119 4
MAY 31 120-150 5
JUN 30 151-180 6
JUL 31 181-211 7
AUG 31 212-242 8
SEP 30 243-272 9
OCT 31 273-303 10
NOV 30 304-333 11
DEC 31 334-364 12
Now, if we look at the question,
(18/200) = 0.09 ~ = 1
If 18 divide with 200, result approx. equals to 1
(200/18) = 11.1
Similar way, if we divide 200 with 18, the result equals to 11.1. If we see above range, 11.1 belongs to
first month “Jan” which returned value “1”
i.e., Select month (1) = 1
So, finally from the problem statement, for both (18/200) and (200/18) equals to “1” as required.
Kindly review above procedure and let me know feedback.
Regards, Pavan Kumar
From the below mentioned T-SQL script problem, I figured out the way of deriving “1” and it as follows:
Question: SELECT MONTH (18/200), MONTH (200/18) = 1
Solution:
Total no. of days in a year = 365 days
I have listed all the months and days of a particular year in the below mentioned format. Based on serial
order of months, I have assumed return values i.e.., For Jan =1 and Feb =2 and so on….
Days In
Days In Year
Month
(Range)
Return
Values
Month
Jan 31 0-30 1
FEB 28 31-58 2
Mar 31 59-89 3
APR 30 90-119 4
MAY 31 120-150 5
JUN 30 151-180 6
JUL 31 181-211 7
AUG 31 212-242 8
SEP 30 243-272 9
OCT 31 273-303 10
NOV 30 304-333 11
DEC 31 334-364 12
Now, if we look at the question,
(18/200) = 0.09 ~ = 1
If 18 divide with 200, result approx. equals to 1
(200/18) = 11.1
Similar way, if we divide 200 with 18, the result equals to 11.1. If we see above range, 11.1 belongs to
first month “Jan” which returned value “1”
i.e., Select month (1) = 1
So, finally from the problem statement, for both (18/200) and (200/18) equals to “1” as required.
Kindly review above procedure and let me know feedback.
From the below mentioned T-SQL script problem, I figured out the way of deriving “1” and it as follows:
Question: SELECT MONTH (18/200), MONTH (200/18) = 1
Solution:
Total no. of days in a year = 365 days
I have listed all the months and days of a particular year in the below mentioned format. Based on serial order of months, I have assumed return values i.e.., For Jan =1 and Feb =2 and so on….
Month DaysInMonth DaysInYear(Range) ReturnValues
Jan 31 0-30 1
FEB 28 31-58 2
Mar 31 59-89 3
APR 30 90-119 4
MAY 31 120-150 5
JUN 30 151-180 6
JUL 31 181-211 7
AUG 31 212-242 8
SEP 30 243-272 9
OCT 31 273-303 10
NOV 30 304-333 11
DEC 31 334-364 12
Now, if we look at the question,
(18/200) = 0.09 ~ = 1
If 18 divide with 200, result approx. equals to 1
(200/18) = 11.1
Similar way, if we divide 200 with 18, the result equals to 11.1. If we see above range, 11.1 belongs to
first month “Jan” which returned value “1”
i.e., Select month (1) = 1
So, finally from the problem statement, for both (18/200) and (200/18) equals to “1” as required.
Kindly review above procedure and let me know feedback.
From the below mentioned T-SQL script problem, I figured out the way of deriving “1” and it as follows:
Question: SELECT MONTH (18/200), MONTH (200/18) = 1
Solution: Total no. of days in a year = 365 days
I have listed all the months and days of a particular year in the below mentioned format. Based on serial order of months, I have assumed return values i.e.., For Jan =1 and Feb =2 and so on….
Example :
Month: JAN DayInMonth: 30 DayInYear(Range): 0-30 Return Values : 1
Month: FEB DayInMonth: 28 DayInYear(Range): 31-58 Return Values : 2
Month: MAR DayInMonth: 31 DayInYear(Range): 59-89 Return Values : 3
Month: APR DayInMonth: 30 DayInYear(Range): 90-119 Return Values : 4
So on ……….
Month: DEC DayInMonth: 31 DayInYear(Range): 334-364 Return Values :12
Now, if we look at the question,
(18/200) = 0.09 ~ = 1
If 18 divide with 200, result approx. equals to 1
(200/18) = 11.1
Similar way, if we divide 200 with 18, the result equals to 11.1. If we see above range, 11.1 belongs to first month “Jan” which returned value “1”
i.e., Select month (1) = 1
So, finally from the problem statement, for both (18/200) and (200/18) equals to “1” as required.
Kindly review above procedure and let me know feedback.
Please review and let me know whether my approach of deriving is correct or not?
Regards,
Pavan Kumar
From the below mentioned T-SQL script problem, I figured out the way of deriving “1” and it as follows: Question: SELECT MONTH (18/200), MONTH (200/18) = 1 Solution: Total no. of days in a year = 365 days I have listed all the months and days of a particular year in the below mentioned format. Based on serial order of months, I have assumed return values i.e.., For Jan =1 and Feb =2 and so on…. Example : Month: JAN DayInMonth: 30 DayInYear(Range): 0-30 Return Values : 1 Month: FEB DayInMonth: 28 DayInYear(Range): 31-58 Return Values : 2 Month: MAR DayInMonth: 31 DayInYear(Range): 59-89 Return Values : 3 Month: APR DayInMonth: 30 DayInYear(Range): 90-119 Return Values : 4 So on ………. Month: DEC DayInMonth: 31 DayInYear(Range): 334-364 Return Values :12 Now, if we look at the question, (18/200) = 0.09 ~ = 1 If 18 divide with 200, result approx. equals to 1 (200/18) = 11.1 Similar way, if we divide 200 with 18, the result equals to 11.1. If we see above range, 11.1 belongs to first month “Jan” which returned value “1” i.e., Select month (1) = 1 So, finally from the problem statement, for both (18/200) and (200/18) equals to “1” as required. Kindly review above procedure and let me know feedback.
Please review and let me know whether my approach of deriving is correct or not?
Regards, Pavan Kumar
On Mon, May 25, 2015 at 2:42 PM, pavan kumar wrote:
> From the below mentioned T-SQL script problem, I figured out the way of > deriving “1” and it as follows: > > Question: SELECT MONTH (18/200), MONTH (200/18) = 1 > > Solution: > > Total no. of days in a year = 365 days > > I have listed all the months and days of a particular year in the below > mentioned format. Based on serial > > order of months, I have assumed return values i.e.., For Jan =1 and Feb =2 > and so on…. > > Days In > > Days In Year > > Month > > (Range) > > Return > > Values > > Month > > Jan 31 0-30 1 > > FEB 28 31-58 2 > > Mar 31 59-89 3 > > APR 30 90-119 4 > > MAY 31 120-150 5 > > JUN 30 151-180 6 > > JUL 31 181-211 7 > > AUG 31 212-242 8 > > SEP 30 243-272 9 > > OCT 31 273-303 10 > > NOV 30 304-333 11 > > DEC 31 334-364 12 > > Now, if we look at the question, > > (18/200) = 0.09 ~ = 1 > > If 18 divide with 200, result approx. equals to 1 > > (200/18) = 11.1 > > Similar way, if we divide 200 with 18, the result equals to 11.1. If we > see above range, 11.1 belongs to > > first month “Jan” which returned value “1” > > i.e., Select month (1) = 1 > > So, finally from the problem statement, for both (18/200) and (200/18) > equals to “1” as required. > > Kindly review above procedure and let me know feedback. > > Regards, > Pavan Kumar > > On Wed, May 20, 2015 at 7:01 AM, Journey to SQL Authority with Pinal Dave
Month Function returns 1 when Date is not specified.
SELECT MONTH(18/200) results in 1 because
SELECTMONTH(0) (18/200 is 0). SQL Server by default results MONTH(0) as 1 and YEAR(0) as 1900 and DAY(0) as 1.
SELECT MONTH(200/18) results 11. MONTH() function returns 1 when Date is not passed.
If date contains only a time part, the return value is 1, the base month.
As it gets converted to days 18/200 = 0 is in 1st month and 200/18 = 11 is also 1st month hence the result is 1 in both the cases
SQL Server tries to convert the input to the `MONTH` function to a `datetime`. Apparently, when it’s fed a numeric value and it has to come up with a date, it computes `’1900-01-01’` (default value for date) + as many days as there are in the number.
The values `18/200` and `200/18` are `0` and `11` respectively, resulting in the dates `1900-01-01` and `1900-01-12`.
This can be verified by running:
select convert(datetime, 0)
select convert(datetime, 11)
Both dates have the month `1`, which explains the answer.
The result from 18/200 is zero (both values are integer so SQL uses integer division). The result from 200/18 is 11 (again, integer).
The first one is interpreted by SQL a 1/1/1900, and the second as 1/11/1900, thus both are month = 1.
Hi Pinal,
Reason is, Month will consider a default date time value as “1900-01-01′. So, if there is any error in getting month calculation, it will consider the default date time and gives the result as “1”. This reulst not only for MONTH(18,200) OR MONTH(200/18). Even if you enter parameter as MONTH(”) it also returns the same output. If we try like this MONTH(‘a’) or MONTH(‘0’) then it throws a conversion error.
This error is not a calculation error. It’s a conversion error, try to convert a varchar value into datetime. That’s why it throws an error.
Thanks,
Damu
Hi Pinal,
Below is the answer.
If you convert 18/200 = 0
When it pass to Month(0) – It will be no of days added to 1900-01-01 which is again same so it will return 1
If you convert 200/18 = 11.11
When it pass to Month(11.11) – It will be no of days added to 1900-01-01 which is ‘1900-01-12’ so it will return 1