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 expression resolves to an integer. MONTH() function treats the integer as datetimeoffset and returns 1 which corresponds to January month of the year 1900.
The month function is returning the default result of 1 because it is treating the parameters as a time value.
Because:
1) 18/200 evaluates to 0 and Select Month(0) is 1 because SQL evaluates Month(0) as 01-Jan-1900
2) 200/18 evaluates to 11 and in Select Month(11), 11 is evaluated as time and hence Select Month(11) gives 1 which is the base month.
Thanks.
Prateek
From sql bol
it says
https://docs.microsoft.com/en-us/sql/t-sql/functions/month-transact-sql?view=sql-server-2017
”
For MONTH(0)
The argument for date is the number 0. SQL Server interprets 0 as January 1, 1900.”
So when ever you pass an integer value to the month function it gives back the month value
by adding the integer value to this base date and gives back the month value of the new date.
to verify
select month(0) –returns 1 for month ie January 1, 1900
SELECT DATEADD(DD,0,’January 1, 1900′)
SELECT DATEADD(DD,35,’January 1, 1900′) — February 5, 1900
SELECT MONTH(35) — this returns 2 for month February
so in this case
SELECT MONTH(18/200), MONTH(200/18)
SELECT 18/200 –0
SELECT 200/18 –11
so actual inputs become
SELECT MONTH(0), MONTH(11)
SELECT DATEADD(DD,0,’January 1, 1900′) –January 1, 1900
SELECT DATEADD(DD,11,’January 1, 1900′) –January 12, 1900
Both the dates lies in the month of January so it returns 1
The Month () Function will use the number of days starting from January month in giving the results. For eg Month(0-30) will give result as 1 because these days will fall in January month. If you pass Month(31) will give the result as 2 as the day 31 of an year falls in February that is month 2 and it goes on.
Select CONVERT(datetime,0,101) will give 1900-01-01 00:00:00.000
Select CONVERT(datetime,11,101) will give 1900-01-12 00:00:00.000
Therefore the numbers can be converted to the datetime object. The number 0 will give sys.mindate. Each decimal point indicates that it is the time part in the dateime object.
Select CONVERT(datetime,1,101) will give 1900-01-01 00:00:00.000 will give the Jan 2 1900 as the answer. That means 0 indicates the sys.mindate (Jan 1 )and 1 indicate Jan 2
Both values, 0.09 and 11.1111… Represent days in January of 1900.
How spammy are the folks at embarcadero?
18/200 = 1 and 200/18 = 11 as both are treated as INTEGER. While using 1 in MONTH function SQL Server converts “1” to January 1, 1900 then applies the month function. The Month number of January is 1. While using 11 in MONTH function SQL Server converts that to January 12, 1900 then applies the month function. The Month number of January is again 1. So in both case answer is 1.
Month take input no of days and convert it to month deciding 30 and then mode 12 .
so 18/200 =0 means 1
200/18 = 11
then also 1
Month Function accepets Date as a input
if you give input as 18/200
it internally converts to date
i.e. convert(datetime, 18/200) => 18/200 = 0
0 tends to the date ‘1900-01-01 00:00:00.000’ and month of this date is 1 so its returning 1
similarly when you do 200/18, 200/18 evaluates to 11 and converting 11 to datetime returns ‘1900-01-12 00:00:00.000’
the month of this date is 1 so output is 1
Contest 1: Month(200/8) when month function contains only time value it always take base month1 jan
Month function is returning its default value “1” as input is 0,11. Is is treating it as time stamp and returning default month “1”
If date contains only a time part, the return value is 1, the base month.
So,
SELECT MONTH(18/200), MONTH(200/18)
Look like
SELECT MONTH(0), MONTH(11) after arithmetic operation
So any time value in month function it will return 1. that why both return 1.
Month function needs date as parameter and hence in give 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
Hi Pinal,
if date contains only the time part the return value is always 1 (the base month)
so, the arguments passed will be considered as time and always returns 1 ( the base month)
SELECT MONTH(18/200), MONTH(200/18) query return 1,1 because it consider record as number of days then divide by month in which this number exist.if we run script such as SELECT MONTH(121) script out put is 5 as 4*30=120 and 121= exist in 5 month.So that the reason it will show 1,1
200/8=11 it exist in first month that why it return 1 same for 18/200.
As per my knowledge
Here,
month function takes date as input parameter and 18/200 and 200/18 convert to default date of sql server that is “1900-01-01 00:00:00.000” so month function returns integer as 1 for January.
Hi Pinal,
When we execute the above script, the sql server takes default time ‘1900-01-01 00:00:00.000’ and calculates the month by adding the result value with the date ‘1900-01-01’.
The Sql Server month function accepts numeric values and it will throw an error only if specified under quotes.
for eg:
If we specify select month(31) it will return 2 as result because 31 days will added with the date ‘1900-01-01’.
1900-01-01 00:00:00.000 + 31 days= 1900-02-01 00:00:00.000
As same
SELECT MONTH(18/200), MONTH(200/18)
as
18/200=0.09
1900-01-01 00:00:00.000 + 0.09 days= 1900-01-01 00:00:00.000 = Result as 1
and
200/18=11
1900-01-01 00:00:00.000 + 11 days= 1900-01-12 00:00:00.000 = Result as 1
SELECT MONTH(‘1900-01-01 00:00:00.000’),MONTH(‘1900-01-12 00:00:00.000’)
as same result’s were 1 .
Thanks in Advance
Month – Returns an integer
Month Returns an integer so result MONTH(18/200) of is 1.
MONTH(200/18) =Month(11) – SQL Server interprets 11 as January 11, 1900, So it result 1.
If date contains only a time part, the return value is 1, the base month.