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
Contest 1: That is because the MONTH function is Implicitly converted into Datetime format and gets the Month from the result.
————————————-
SELECT MONTH(18/200), MONTH(200/18)
returns –
1 1
————————————-
Month() takes in a date parameter
Case Month(18/200) :
18/200 – returns 0
The argument for date is the number 0. SQL Server interprets 0 as January 1, 1900. as shown by the query below –
select Cast(0 as datetime) returns “1900-01-01 00:00:00.000”
Thus Month(0) – returns 1 as january
Case Month(200/18) :
200/18 – returns 11
The argument for date is the number 0. SQL Server interprets 0 as January 12, 1900. as shown by the query below –
select Cast(11 as datetime) returns “1900-01-12 00:00:00.000”
Thus Month(11) – returns 1 as january
Select month(18/200)
Explanation:
Select (18/200)
Output 0
Selec month(0)
Output is 0
Select month(200/18)
Explanation:
Select(200/18)
Output 11
Select month(1)
Output 1
Hello Pinal,
18/200 is first converted to numeric value. Hence it returns 0. When it is converted to DATETIME or SMALLDATETIME , SQL Server interprets 0 as January 1, 1900. ‘Select MONTH(0)’ query will return 1.
200/18 is also first converted to numeric value. Hence it returns 11. When it is converted to DATETIME it returns ‘1900-01-12 00:00:00.000’ and when it is converted to SMALLDATETIME it returns ‘1900-01-12 00:00:00’
Hence the query SELECT MONTH(18/200), MONTH(200/18) when internally executed will pick months from ‘January 1, 1900’ and ‘January 12, 1900’. So the month value returned is 1 for both the columns. And it is correct output.
Note: MONTH() function of SQL takes any of [time, date, smalldatetime, datetime, datetime2, or datetimeoffset] data types as parameter. However the above output will only be observed in case of date & smalldatetime data types. For remaining data types it will throw an error.
Small correction: Instead of Numeric it should re read as Integer in above answer.
Hello Pinal,
18/200 is first converted to Integer value. Hence it returns 0. When it is converted to DATETIME or SMALLDATETIME , SQL Server interprets 0 as January 1, 1900. ‘Select MONTH(0)’ query will return 1.
200/18 is also first converted to Integer value. Hence it returns 11. When it is converted to DATETIME it returns ‘1900-01-12 00:00:00.000′ and when it is converted to SMALLDATETIME it returns ‘1900-01-12 00:00:00′
Hence the query SELECT MONTH(18/200), MONTH(200/18) when internally executed will pick months from ‘January 1, 1900′ and ‘January 12, 1900′. So the month value returned is 1 for both the columns. And it is correct output.
Note: MONTH() function of SQL takes any of [time, date, smalldatetime, datetime, datetime2, or datetimeoffset] data types as parameter. However the above output will only be observed in case of date & smalldatetime data types. For remaining data types it will throw an error.
SELECT MONTH(18/200)
–Explanation
SELECT (18/200)
–Output 0
SELECT MONTH(0)
–Output 1
SELECT MONTH(200/18)
–Explanation
SELECT (200/18)
–Output 11
SELECT MONTH(11)
–Output 1
Hi Pinal,
Interesting puzzle. What I like about it is how the date is disguised as a division of 2 numbers. :) Well, in my opinion, because neither 18 nor 200 is a valid month, SQL Server considers the ‘date’ parameter for MONTH function as 18th January and returns 1 in both cases as the numeric representation of January.
Thanks,
Mandar
……
-63
-62 here oct(10)
—————————————————–
-61
.
.
-33 30 days for Nov(11)
-32
—————————————————-
-31
-30
.
. 31 days for Dec(12)
-2
-1
——————————————————-
0 <———-18/200
1
2 31 days for Jan(1)
.
.
11 <———-200/18
.
.
30
—————————————————–
31
32
33 28 days for Feb(2)
.
.
58
—————————————————–
59
. here march(3)
.
Like that it gives month number.
SELECT MONTH(18/200), MONTH(200/18)
Result is:
1 1
Why 1 for MONTH(18/200) ?
18/200 = 0
Hence Month(0). The date argument is 0 here.
SQL Server interprets 0 as January 1, 1900.
January is the 1st Month.
So 1 is the Answer.
Why 1 for MONTH(200/18) ?
200/18 = 11
Hence Month(11). The date argument is 11 here.
Since SQL Server interprets 0 as January 1, 1900.,
11 will be interpreted as January 12, 1900.
January is the 1st Month.
So 1 is the Answer.
From
====
Manjula T
[email removed]
India
18/200 and 200/18 evaluate to a number which is accepted by the Month function as being a time. SQL Server BOL states “If date contains only a time part, the return value is 1, the base month”, hence why the Month function returns 1.
Hi Pinal,
SQL Server consider unknown date as 01/01/1900 (Default). So any unknown date month function consider “1” as first month of date “01/01/1900”. That’s my prediction…
SELECT MONTH(18/200), MONTH(200/18)
returns 1, 1 is because 18/200 and 200/18 are first converted to pure numeric then to DATETIME, as following:
1.
SELECT CAST (200/18 AS numeric);
it results : 11
Now SELECT CAST (11 AS DATETIME);
It results : 1900-01-12 00:00:00.000
Now SELECT MONTH(‘1900-01-12 00:00:00.000’);
It results 1 which is showing january month of the year of 1900;
2.
SELECT CAST (18/200 AS numeric);
it results : 0
Now SELECT CAST (0 AS DATETIME);
It results : 1900-01-01 00:00:00.000
Now SELECT MONTH(‘1900-01-01 00:00:00.000’);
It results 1 which is showing january month of the year of 1900;
Reason : The DATETIME data type, behind the scenes, is a long integer, with zero beginning at 12:00 AM morning of January 1st, 1900,
the MONTH function converts these numbers to pure numeric and then to DATETIME and then to Month.
If we pass number value into Month function then it accept that value as a default value “January 1, 1900” .so it return value as 1 because SELECT MONTH(‘January 1, 1900’) return 1 .
Sql server accept defalut year as 1990 , month as january and day return cycling value from 1 to 30 and incremented by 1 . means if we enter 0 then it will return 1 . if enter 30 then 31 and if we enter 31 then it started again with 1 so return 1 .
The MONTH(?) function is looking for ? to be time, date, smalldatetime, datetime, datetime2, or datetimeoffset. 18/200 and 200/18 are interoperated to be a time part. If date contains only a time part, the return value is 1, the base month.
The Month() function expects an expression and it tries to treat any value you have entered as a datetime and returns the integer value (of the month of the the datetime value you gave). Now, 18/200 is 0 in SQL and 200/18 is 11 (Integer division). Now, if you convert these values (0, 11) into datetime, you get “1900-01-01 00:00:00.000” and “1900-01-12 00:00:00.000” respectively. As you can see, the Month part of both of these dates is January i.e., 1.
DAae range in Sql starts from 01-01-1900.
Integer values represent days count.
The value of MONTH(18/200) represents date 01-01-1900
The value of MONTH(18/200) represents date 12-01-1900
So on selecting the Month it gives one
18/200 = 0 — int value
The argument for date is the number 0. SQL Server interprets 0 as January 1, 1900.
200/18 = 11 or January 12, 1900
SELECT day (18.000 / 200), month (18 / 200), year (18 / 200)
SELECT day (200 / 18), month (200 / 18), year (200 / 18)
SELECT day (31), month (31), year (31)
SELECT DATEPART ( hour , 18.00 / 200 )
Both 18 and 200 are integers and and SQL Server will try to implicitly cast the 18/200 = 0.09 to INT, and it rounds to zero.
And to the month, both 18/200 = 0, and 200/18 = 11 are both valid Julian Date inputs for the Month function and both Julian day 0 and day 11 fall within the month of January.
Note, that if you were to run:
SELECT MONTH(30), MONTH(31) You’ll get month 1 and 2 respectively as expected.
For Column 1, 18/200 returns 0 which is 01-01-1990 so the month for that date is 1 (January) and for column 2, 200/18 returns 11 which is 12-01-1990 so the month for that is 1 (January) as well.
The MONTH function takes a date as a parameter. And the result of the calculations can be implicitly converted to a Date value. This is possible because internally, SQL Server stores DateTime values as integers. Basically, that translate to the number of days before/after 1/1/1900. The MONTH functcion returns 1 because both the calculations end up returning dates in January: 1/1/1900, and 1/11/1900 (i.e. the second calculation’s integer portion of 11 is converted to 1/11/1900).