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 always takes the number of days in a month
e.g month(30) gives 1, so month(0)…. month(30) will result in 1
where as month(31)…. to month(58) gives 2 and so on. Here it is 58 because February has 28 days.
Hence 18/200 = 0.09 and 200/18 = 11.1111 which both are under 30 and so the result is 1
The Month function always takes the number of days in a month
e.g month(30) gives 1, so month(0)…. month(30) will result in 1
where as month(31)…. to month(58) gives 2 and so on. Here it is 58 because febuary has 28 days.
Hence 18/200 = 0.09 and 200/18 = 11.1111 which both are under 30 and so the result is 1
Hi Pinal,
The Month() function in SQL takes DATETIME datatype values as input. So, the input values 18/200 or 200/18 will be converted as DATETIME and gets the Month of that resulting datetime.
Considering MONTH(200/18)
First SQL calculates the 200/18 which is 11 (absolute value)
When 11 is converted to DATETIME, the 11 will be added to the default datetime “1900-01-01 00:00:00.000”. So the output will be “1900-01-12 00:00:00.000”
Hence, the MONTH(‘1900-01-12 00:00:00.000’) = 1 which is January
Try, MONTH(200/2), this will add 100 days to “1900-01-01 00:00:00.000” which will result in “1900-04-11 00:00:00.000”
So, MONTH(200/2) = 4.
Thanks,
Balu
int value for date input will be considered as number of days. when value is 0 the its “1900/01/01”, when 11 then its “1900/01/12”. This can be evaluated by simple test below.
From the result it can be noted that when @cnt = 365 then date starts to “1901/01/01”
declare @cnt int = 0
while @cnt <= 365
begin
SELECT YEAR(@cnt) [Year], MONTH(@cnt) [Month], DAY(@cnt) [Date];
set @cnt = @cnt + 1
end
— The Reason is because the month() function has obtained the month value for both numeric results.
In this case the date value A= 0.09 (18/20) is equal to 1900-01-01 02:09:36.000 on date format, which month value is 01 and for the other date value B= 11.11 (200/18.0) is equal to 1900-01-12 02:39:59.990 on date format, which month value is 01 as well.
Based on the previous statement, the sentence won’t throw out any error message because that function allows to obtain the month value either date values or numeric values (I’m talking about float format).
— Verification:
Declare @vValA numeric(12,6) = 18/200.0
Declare @vValB numeric(12,6) = 200/18.0
select month(@vValA ) MonthA,
month(@vValB)MonthB,
@vValA ValueA,
@vValB ValueB,
convert(datetime, @vValA) DateforValueA,
convert(datetime, @vValB) DateforValueB,
Because MONTH returns the same value as DATEPART (month, date) and If date contains only a time part, the return value is 1, the base month.
It’s because MONTH will always return an integer as long as the expression inside of the params are valid. 18/200 and 200/18 are valid expressions, however, they don’t resolve to valid months, therefore it returns 1.
HI,
18/200 is an integer expression and returns 0 as the result. As SQL converts 0 to the date 1900-01-01 (counting number of days from the start of the date that SQL recognizes), month function would return the result 1.
Similarly 200/18, integer expression returns 11 as the result. 11 would be converted to 1900-01-11. Hence Month function returns 1.
Till 31 month would return 1 and for a value 32 month function would return 2 (e.g. SELECT MONTH(580/18) would return 2 )
Parameter for month () should be provided with date . If one value is provided it will be considered as time and return first month as 1
/*
MONTH returns the same value as DATEPART (month, date).
If date contains only a time part, the return value is 1, the base month.
*/
select TIMEFROMPARTS(18/200,0,0,0,0) –00:00:00
select MONTH(’00:00:00′) –1
select TIMEFROMPARTS(200/18,0,0,0,0) –11:00:00
select MONTH(’11:00:00′) –1
/*
MONTH returns the same value as DATEPART (month, date).
If date contains only a time part, the return value is 1, the base month.
*/
select TIMEFROMPARTS(18/200,0,0,0,0) –00:00:00
select MONTH(’00:00:00′) –1
select TIMEFROMPARTS(200/18,0,0,0,0) –11:00:00
select MONTH(’11:00:00′) –1
/*
MONTH returns the same value as DATEPART (month, date).
If date contains only a time part, the return value is 1, the base month.
*/
select TIMEFROMPARTS(18/200,0,0,0,0) –00:00:00
select MONTH(’00:00:00′) –1
select TIMEFROMPARTS(200/18,0,0,0,0) –11:00:00
select MONTH(’11:00:00′) –1
It returns a value 1 since the default date format in SQL Server is: 01-01-1900.
i.e ~ MONTH(’01/01/1900′) = 1
Sqlvserver stored the date field in number instead of date format.date range 1900-9999 already stored indb ,when we enter the date it will convert into number and return the date against the numeric value. Basically the numeric value is like a index value.it will return the actual date which is stored in particular index location. If you want to know the original date you can use the below query.
Select convert(date,18/200)
Year function accepts date and numeric. It interprets 0 as January 1, 1900. In this case, (18/200)=1 and (200/18)=11 adding the result to January 1, 1900 will still be January, hence returning result as 1.
because SQL Server interupts SELECT YEAR(0), MONTH(0), DAY(0) as 1900-01-01 and and Month (0-30) with return 1 value so SELECT MONTH(18/20) =Select Month(0) this will return 1
,SELECT MONTH(200/18)=Select MONTH(11) this will return 1
SQL Server 18/200 or 200/18 returns integer number,so any integer to the MONTH() function gives January 1, 1900.This is the reason it returns 1 1 as output.
Hi Pinal,
First of all, thanks for starting the contest. I have downloaded the DBArtisan tool mentioned above using my email id (miracle_nitin0411@yahoo.co.in).
Now coming to your first question, why MONTH function return 1 and didn’t throw any error?
The reason for this behavior is that syntax of month function is “MONTH ( date )” and it returns a integer value. So if you are entering any date expression it will return the month number correctly. If not and If date expression contains only a time part, the return value is always 1,which is the base month.
I tried various parameters in this month function and only time, it threw an error was when I gave expression something like this : select month (12/0). The error was “Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered.”
The opposite of this month(0/12) also returned integer value 1.
I hope I am correct.
SELECT MONTH(30); –1st Month
SELECT MONTH(360);–12th Month
SELECT MONTH(GETDATE()); — Current month
SELECT MONTH(500/2) — for this , it will return 9th month.
–So here
SELECT MONTH(18/200), MONTH(200/18)
–which means 0.09 and 11.11.
— Moth function is taking input values as time, date, smalldatetime, datetime, datetime2, or datetimeoffset value.
— For SELECT DAY(11.11) it will take 12 and for SELECT DAY(0.09) it will take 1
–This comes under 30 days. so it will return 1.
Month takes datetime as input, so so converting 18/200 and 200/18 results to the values 1900-01-01 00:00:00.000, 1900-01-12 00:00:00.000 respectively. In both case the month is January
SQL Server stores data for datateime type in integer format, intenally, i.e, if we store a date value ‘2015-05-21’ it will store number of days passed from base date (‘1900-01-01’) .
So when we pass a number to month function it assumes it number of days from base date which results in a new date and it returns the month number of new date formed. Please see code explained.
SELECT DATEADD(DD, 18/200, ‘1900-01-01’)
OUTPUT :
1900-01-01 00:00:00.000
SELECT MONTH(‘1900-01-01 00:00:00.000’)
OUTPUT:
1
SELECT MONTH(18/200)
OUTPUT:
1
SELECT DATEADD(DD, 200/18, ‘1900-01-01’)
OUTPUT:
1900-01-12 00:00:00.000
SELECT MONTH(‘1900-01-12 00:00:00.000’)
OUTPUT:
1
SELECT MONTH(200/18)
OUTPUT:
1
============================= SIMILARLY=================================
SELECT DATEADD(DD, 364, ‘1900-01-01’)
OUTPUT:
1900-12-31 00:00:00.000
SELECT MONTH(364)
OUTOUT:
12
Output of 18/200 = 0 and 200/18 = 11. There fore resultant date arguement is January 1, 1900. Hence Month function returns integer 1 for the month January.
SELECT MONTH(18/200), MONTH(200/18)
SELECT month(0), month(11)
Above script return 1 as a month number. because 0 and 11 belongs to the range 0-30.
if you pass value between 31-58 it will returns 2 (second month number February). So on