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
Month function calcualtes no of days and then on the basis of that calucalte month from 0-30(31 days of jan) it will return 1 , 30+0 to 28 ( 28 days of Feb ) it will return 2 , 30+28+(0 to 31) (31 days of march it will return 3 and so on .
18/200 and 200/18 both fall in range 0-30 range so it is givng same i.e.1
If we are passing numeric value to “MONTH” function, it will add that number of days to “01/01/1900” and return the month of date.
For example we can rewrite the above query like below
Select Month(DATEADD(Day,18/200,’01/01/1900′)), Month(DATEADD(Day,200/18,’01/01/1900′))
DBArtisan is a very useful tool for database developers like me
in sql server 0 as 1 jan 1900
SELECT CAST(18/200 as datetime)
1900-01-01 00:00:00.000
SELECT CAST(200/18 as datetime)
1900-01-12 00:00:00.000
so month is as 1
If date contains only a time part, the return value is 1, the base month.
You have to pass Date in Month() Function.
Like select month(‘2014/12/05’) will return 12 but in this case it will return base month. what ever you pass month(1)….month(1000)etc.. will return 1.
SQL Server interprets 0 as January 1, 1900 and returns the base month if not passed date as argument in Month() function. So if you pass any integer value less than 50, it returns 1. i.e. SELECT MONTH(50) will return 2.
Hi Pinal,
Contest 1 (Month function)
The reason is because the “MONTH” function returns base month i.e. 1st January 1990 if the time value has been passed to it.
So here if you pass
1. 18/200 = 0.09
2. 200/18 = 11.11
So both the above values will be considered as time format and passed to the function without the date value hence this function will return the base value of month. So finally we are getting 1 (January) as our output result.
You can consider any values which has valid time format or pass as an arithmetic operation
e.g. 16/300, 300/16 etc.
The Answer is 1,1 because the both of the numbers are less than 31 days will shows the Month as 1. >=31 will show as 2 and >=59 will show as 3rd month.
Month returns an integer that represents the month of the specified date. But If date contains only a time part, the return value is 1, the base month.
SELECT MONTH(18/200), MONTH(200/18)
In the above case
18/200 = 0.09
200/18 = 11.11
Both values are representing time so the return value is 1 :)
SQL Server treats number 0 as January 1, 1900 for Date. So when we are calculating the Months it is adding the number of days to the January 1, 1900 date and returning the month value for that. So, in case of this example Month(18/200) will add 0 days to January 1, 1900 and the month will still be January. In case of Month(200/18) it will add 11 days to January 1, 1900, so the month will still be January.
Thanks
Akhil
SELECT MONTH(18/200), MONTH(200/18)
First this will evaluate the expression in MONTH().18/200 will evaluate to 0.09 and and 200/18 will evaluate to 11.11. T-sql will consider this as time.If date contains only a time part, the return value is 1 i.e. base month. So MONTH(18/200) and MONTH(200/18) will return 1.
It’s because Month function takes Date as Input Parameter, Date being an expression to resolve datetime and smalldatetime values. Here 18/200 or 200/18 gets converted to Jan 1 1900 12:00AM and Jan 12 1900 12:00AM respectively.
Month Function returns integer value for Month so its returning 1 for January in both outputs.
The expressions 18/200 and 200/18 returns INTs, which are then implicitly converted to DATETIMEs, which then have the months extracted as INTs. These queries show the process:
SELECT
18/200 expression_1 — 0
,CONVERT(DATETIME,18/200) explicit_conversion_1 — 1900-01-01 00:00:00.000
,DATEPART(MONTH,CONVERT(DATETIME,18/200)) month_explicit_conversion_1 — 1
,MONTH(18/200) month_implicit_conversion_1 — 1
SELECT
200/18 expression_2 — 11
,CONVERT(DATETIME,200/18) explicit_conversion_2 — 1900-01-12 00:00:00.000
,DATEPART(MONTH,CONVERT(DATETIME,200/18)) month_explicit_conversion_2 — 1
,MONTH(200/18) month_implicit_conversion_2 — 1
If the expression was quoted, then the query would fail:
SELECT MONTH(’18/200′)
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
OK, here’s my answer for contest 1: in both cases, SQL Server will first evaluate the division (resulting in 0 and 11 respectively). When fed with an integer, the MONTH function will assume 0 as 1900-01-01 and add days as the integer increases. So it’s basically asking what is the month of dates 1900-01-01 and 1900-01-12, which is 1 for both cases.
For the second contest, the e-mail I used is: lrosa at dotz dot com dot br
Hi Pinal,
Month function in SQL Server will return 1 if only time part is being selected. So 18/200 – 0.09 and 200/18 – 1.1
Thats the reason, out SQL Statement returns 1 as result.
The same can be checked in reverse way as well Select month (0.09) and it will also return the same 1.
Thanks!
Pavan
The result 18/200 is 1 int, an implicit conversión of 1 to datetime is 1900-01-02, ht month for that date is 1 the same for 200/18 is 11, the result of the conversion to datetime is 1900-01-12 finally the scalar function month returns 1
Hi Pinal,
Expression given inside braces automatically convert into default date i.e. ‘19000101’ and add number that is returned by the expression .
e.g. 200/18= 11 (day of year) and therefore result is one and same with other.
Thanks,
Anuj Kumar
The month function must be passed one of the following datatypes: time, date, smalldatetime, datetime, datetime2 or datetimeoffset. Both 18/200 and 200/18 return an integer result, and must be converted to one of those datatypes before the month function will work. SQL performs an implicit conversion to these data types, because datetimeoffset, datetime, datetime2, smalldatetime, date, and time all have higher precedence than int. SQL then appears to interpret the integer value as the number of days since day one, which is 1/1/1900 (I need to test this further… never thought to work with dates this way). 18/200 returns 0, and so is converted to 1/1/1900; 200/18 returns 11, which is converted to 1/12/1900, as seen by the following query. select convert(datetime,18/200),convert(datetime,200/18)
From there, it is easy to see that month should return 1 for both expressions.
Contest 1: The Month function takes integers (or float for that matter) as days of the month. If you supply it 0-30, it will give you month 1 (January 1st to 31st), 31 will give 2 (February 1st), -1 will give 12 (December 31st). When you divide 18/200 that value corresponds to some time within the first day since 18/200 < 1, which will correspond again to January or month 1. Likewise, 200/18 is < 10, and because 10 is still in the first month of the year it returns month 1 again (or January). Yea?
The month function when and integer inserted into the function calculates the month based on the number of days. Since both 18/200 and 200/18 are less than 31, they evaluate the first from 1900,1,1. If you input select month(558/18).