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 returns an integer and requires a date argument. If the argument is time, the result is 1, the base month. The function must be interpreting the quotients 18/200 and 200/18 as time, and are therefore returning the integer 1.
See https://docs.microsoft.com/en-us/sql/t-sql/functions/month-transact-sql?view=sql-server-2017
Hi Pinal,
As this is a expression and not a valid date format, so it is taking the Base month 1 that is January as default date for SQL is 01 Jan 1900 , so it is returning int value 1 for month January.
Thanks
Atul Upadhyay
select 18/200 -> 0 (integer)
select 200/18 -> 11 (integer)
SELECT CAST(0 AS DATETIME) -> 1900-01-01 00:00:00.000 (January Month)
SELECT CAST(11 AS DATETIME) -> 1900-01-12 00:00:00.000 (January Month)
Parameter of MONTH function is date
So if you run MONTH(18/200) then it will executed as MONTH(‘1900-01-01 00:00:00.000’) & it will return 1 (means January)
& if you run MONTH(200/18) then it will executed as MONTH(‘1900-01-12 00:00:00.000’) & it will also return 1 (means January)
Congratulation!!!
Thank you :)
18/200 -> 0 (integer)
200/18 -> 11 (integer)
SELECT CAST(0 AS DATETIME) -> 1900-01-01 00:00:00.000 (January Month)
SELECT CAST(11 AS DATETIME) -> 1900-01-12 00:00:00.000 (January Month)
Parameter of MONTH function is date
So if you run MONTH(18/200) then it will executed as MONTH(‘1900-01-01 00:00:00.000’) & it will return 1 (means January)
& if you run MONTH(200/18) then it will executed as MONTH(‘1900-01-12 00:00:00.000’) & it will also return 1 (means January)
Congratulations :)
Thank you :-)
Hi Pinal, as the value is treated as Numeric value(expression) not as a string/Date. Hence you will always get value based on the calculated value e.g. 200/80.
The MONTH function in SQL returns the month of the date specified as the parameter. And if we try to pass a integer value instead of date then SQL converts that integer value to its default date i.e. 1900-01-01 and therefore the function always returns the month as 1.
when you pass zero to month() then SQL Server interprets it as 1 January 1900 date,when you will pass numbers like 18/200 ,it gives 0.09 which is treated as zero and consider date as 1 Jan 1900 date and month() hence return it’s jan month number.Same thing is applied to year() and day() which return 1900 and 1 respectively
In previous comment by mistake i have written 1 Jan 1900 but it is 0 Jan 1900.
when you pass zero to month() then SQL Server interprets it as 0 January 1900 date,when you will pass numbers like 18/200 ,it gives 0.09 which is treated as zero and consider date as 1 Jan 1900 date and month() hence return it’s jan month number.Same thing is applied to year() and day() which return 1900 and 1 respectively
And when you pass month(200/18) SQL Server consider it as 12 Jan 1900 by rounding 11.11 and still month Jan hence return 1 for this also.
SQL Server interprets “18/200” or “200/18” as “January 1, 1900” or “1900-01-01 00:00:00.000”,
To verify above statement:
SQL: SELECT CAST(18/200 AS DATETIME)
Output: 1900-01-01 00:00:00.000
This is because, the Month function return “January” or “1” in result/output of SQL Statement: SELECT MONTH(18/200), MONTH(200/18).
select month(18/200),month(200/18)
result:1,1
reason is generally sql server gives the default date ” 1990-01-01 “,when you pass these values it generates default 1 for boath conditions.
Because it counts 30(quotient) as a month of 30 days. if quotient is more than 30 it increase value by 1. Again in same way it will count and increase the value by 1.
EX if we divide 557/18 count will be 1 because quotient is 30 now, if we take 558/18 quotient will be 31 so result will be 2 in this condition.
Hello Sir,
It is because, if we pass only time part the return value is always 1, the base month.
Here we are passing interger value which is consider as a time part.
Thanks,
Rushik
Because 200/18=11 is converted to 1900-01-12, that have 1 as month, and 18/200=0 is converted to 1900-01-01, that have month=1 too. Isn’t an error!
SELECT CAST(200/18 AS DATETIME), YEAR(200/18), MONTH(200/18),DAY(200/18)
SELECT CAST(18/200 AS DATETIME), YEAR(18/200), MONTH(18/200),DAY(18/200)
Extra: If date returns only the time part, the return of month function is always 1, as shown in msdn:
https://docs.microsoft.com/pt-br/sql/t-sql/functions/month-transact-sql?view=sql-server-2017
If date contains only a time part, the return value is 1, the base month.
It converts the integers to datetime (1900-01-01 + the result of the division in days) then the calls the MONTH with it.
SELECT
18/200, — 0
200/18, — 11
convert(datetime,18/200), — 1900-01-01 00:00:00.000
convert(datetime,200/18) –1900-01-12 00:00:00.000
Each of the dates are in the first month.
E-mail used for download: [email removed]
Contest 1:
1.) Month(0) –SQL Server interprets 0 as January 1, 1900. Hence the ouput is 1.
2.) Month(11)–SQL Server interprets this as January 11,1900. Hence the ouput is 1.
If in case we give Month(32) we get ouput as 2
The answer is that the expressions passed as parameters to the month function first will be calculated with the results 0 and 11. The expressions will then be implicitly converted into datetime values which is what the month function expects. As the values 0 and 11 will be understood as the day part of a datetime value the unspecified date component is set to the default value 1900-01-01 to which the value is added, giving ‘1900-01-01 00:00:00.000’ and ‘1900-01-12 00:00:00.000’ as the proper datetime values. The month function will clearly return 1 for both. This is documented in the documentation for CAST and CONVERT.
MONTH(18/200) means MONTH(0) which is intepreted as default datetime in SQL Server( 1900-01-01).Same for MONTH(200/18), upto MONTH(30) it will be intepreted as datetime in SQL Server( 1900-01-31) .
FromOADate
When we execute SELECT MONTH(18/200) it show 1
When we execute SELECT MONTH(200/18) it show 1
Lets execute the values without Month
SELECT 18/200 it Result 0
SELECT 200/18 it Result 11
Now pass the executed values to Month
SELECT MONTH(0)
Result 1
SELECT MONTH(11)
Result 1
Month actually executed OADate to calculate month;
i have a example,
for e.g (39521) is the OA Code of “3/14/2008”
Now execute this OA Code in month
Select MONTH(39521)
It shows you the Result as 3
3 is the month in above date,
By passing 0 and 11 to Month it calculate the date and shows month as 1
Thanks
when you give the argument as integer or double decimal then month function of the SQL will count the days and give the month according to the days.
so if you do SELECT MONTH(60) then it will return 3 because days is 60 so 31 of January and 28 of feb Total 58 of 60 will be the 3rd month same if you give just SELECT MONTH(59.11) same result will be there 3 but if you give SELECT MONTH(10) then it will give 1st month since year’s 10th day is first month. now MONTH(18/200) is 0.09 of the it is the first month and same for the 200/18 is 11.11111111111111 then it is 11th day of year so it will return 1st month.
Contest 1
Great question. Since the values are not wrapped in quotes they are handled as datetimeoffsets, thus no errors. The offset value is in days from 1900-01-01 which resolves to 1900-01-12 for 200/18 and 1900-01-01 for 18/200. Both of those dates being in January results in both values being 1.
You can test it out by simply casting the values to datetime.
select month(200/18), cast(200/18 as datetime);
select month(18/200), cast(18/200 as datetime);