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
Select month(18,200),month(200/18) giving 1 value because first of all it did 200/18 that results 11.11 so from that SQL server taking integer value that is 11. So till now it shows 11 days in month function. Now Query becomes select month(11) so in that 11 days must be divide by 30 for evaluate month. So after that 11/30 outputs 0.so at last the month will show 1 because in that function it output upper limit of that month.So finally output comes 1 and it does not throw any exception.
SELECT MONTH((18/200)%365), MONTH((200/18)%365)
SELECT MONTH(18/200), 18/200 evaluates to 0 as date parameter which is base month. hence the result 1.
SELECT MONTH(200/18), 200/18 evaluates to 1 as date parameter which is the first month of the base year.
[email address]
Hi Pinal,
After thinking on it i have found the logic behind it.
By default SQL Server has Date : 01/01/1900
So now if I pass 0 as argument in above function it will return 1 value. Logic i Found for month is below.
Declare @a int=200
Declare @b int=18
Declare @C int=@a/@b
Declare @SupposeDate date
set @SupposeDate= Dateadd(day,@c,’01/01/1900′)
Select Month(@SupposeDate),Month(@c)
SQL Server add that number of days in ’01/01/1900′ that we have passed and than check the month of that date and it will return as an output.
That is what I am thinking. Please let me know your feedback.
Thanks & Regards,
Rikhil
Hi Pinal ,
I am seeing my comment status is under waiting for moderation? I posted it yesterday. Why it is taking too long time to update?
18/200 = 11; 200/18 = 0; when one execute MONTH(typeof(int)), then the “date” used is the number of days date from 1900-01-01, where Jan. 1 = 0 and Jan. 31 = 30, and Feb. 1 = 32.
rogge
addendum… and oops Feb 1= 31… so MONTH(31) = 2.
We need to look at the number of days added to 1900-01-01 after performing the division operation for the numbers provided in braces i.e 18/200 = 0 and 200/18 = 11and the way it works is as follows.
Select MONTH(18/200) = Select MONTH(0)= Select MONTH(dateadd(dd,0,0)) = Select MONTH(dateadd(dd,0,’1900-01-01′))
Select MONTH(200/18) = select MONTH(11) = Select month(dateadd(dd,11,0))= Select MONTH(dateadd(dd,11,’1900-01-01′))
Is it because dayofyear (200/18)=11 is updated to 12 as day 1-1-1900 starts with zero index,so it month(12th jan 1900). similarly 18/200 is 1 month(1st jan 1900) both returns jan whose integer value is 1
Hi Pinal,
Answer for T-SQL contest is as below.
SELECT MONTH(18/200), MONTH(200/18)
MONTH(18/200)
—————————
First it evaluates 18/200 which is 0, so query becomes MONTH(0)
In SQL Server argument 0 in MONTH(0) is considered to January 1 1990.
So answer is 1 because it is first month.
MONTH(200/18)
———————————–
First 200/18 is evaluated as 11.
argument in MONTH(11) is same as above considered to be the date of first month, so answer is 1
if we trying to convert any integer value into date time SQL server always consider date “01/01/1900 00:00:00” .. SELECT CONVERT(DATETIME, 2)
what it do is trying to convert (200/18) to datetime value like this:
select CONVERT (datetime,(18/200)) —>>>1900-01-01 00:00:00.000
select CONVERT (datetime,(200/18)) —>>>1900-01-12 00:00:00.000
then it Extract the month from that date ,,,which is January in both cases
Hi Pinal,
The 18/200 or 200/18 results an integer value. this resultant in turn is treated as time part by the month function and hence the default is returned as 1. If date contains only a time part, the return value is 1, the base month.
Hello Pinal,
Following is my response:
CASE OF: ‘SELECT MONTH(18/200)’
The argument in Month function represents ‘Date’ in ‘int’ format. 18/200 gives zero as integer value, thus the expression becomes Month(0) & SQL Server interprets 0 as January 1, 1900. Therefore, it returns 1 which is January.
Further, it is interesting to note that any date/datetime in SQL Server can be represented in integer format. Have a look at the following:
select cast(0 as datetime) — returns ‘1900-01-01 00:00:00.000’
select cast(cast(0 as datetime) as datetime) — returns ‘1900-01-01 00:00:00.000’
select month(cast(0 as datetime)) — returns 1
select month(cast(cast(0 as datetime) as datetime)) –returns 1
So, effectively integer 0 represents ‘1900-01-01 00:00:00.000’, hence MONTH(18/200)==MONTH(0)==MONTH(‘1900-01-01 00:00:00.000’) returns 1 which is January.
CASE OF: ‘SELECT MONTH(200/18)’
Similarly, MONTH(200/18)=MONTH(11)==MONTH(‘1900-01-12 00:00:00.000’) returns 1 which is January again.
select cast(11 as datetime) — returns ‘1900-01-12 00:00:00.000’
select cast(cast(11 as datetime) as datetime) — returns ‘1900-01-12 00:00:00.000’
select month(cast(11 as datetime)) — returns 1
select month(cast(cast(11 as datetime) as datetime)) –returns 1
@Pinal: Your posts are always a delight to read before starting my day. They are short, spot-on & serve as a new learning everyday. Your recent posts of ‘PowerShell script’ and ‘net helpmsg’ were also very helpful, they come handy during production issues.
The above question was equally interesting to solve.
Thanks for sharing your knowledge!
Regards!
In SQL Server, The Month() method takes date as input parameter and returns month of the entered date. However when an integer value is passed to this function, it counts the number of days starting from 1900-01-01 00:00:00.000 and returns the month of it.
Hence when 18/200 is passed it returned one ie it converted it to value1900-01-01 00:00:00.000 and returned 1. Similarly when 200/18 is passed, the same is converted to 1900-01-12 00:00:00.000 and the method again returns one.
PS: run select cast(18/200 as datetime) for an example.
Kind regards,
Ram Kripal Singh.
In SQL Server, The Month() method takes date as input parameter and returns month of the entered date. However when an integer value is passed to this function, it counts the number of days starting from 1900-01-01 00:00:00.000 and returns the month of it.
Hence when 18/200 is passed it returned one ie it converted it to value1900-01-01 00:00:00.000 and returned 1. Similarly when 200/18 is passed, the same is converted to 1900-01-12 00:00:00.000 and the method again returns one.
select 18/200, 200/18 will return 0 and 1.
Month functions takes input a date and it implicitly converts 0 and 1 as datetime
and deafault starting value as 1900-01-1 00:00:00.000 and 1900-01-12 00:00:00.000
respectively.
SELECT Month(0), Month(1) —
After applying month function will return month part as 1, 1
If date contains only a time part, the return value is 1, the base month.
It assumes that results of the division – integer- as time part.
The result of the expression 18/200 and 200/18 results to 0 & 11 respectively. The integer value gets added to 1900-01-01 when applied with a date related function i.e. Month. Basis the above results it’ll result into 1900-01-01 and 1900-01-12 respectively. Since the month value for both the dates is 1 hence Month functions returns the result as 1 for both the expressions.
Hope the answer clarifies the fact behind this…
—–The Question ? –Why returns 1, 1
SELECT MONTH(200/18),MONTH(18/200)
–Declaring a date variable and a Character Variable
DECLARE @TodayDate DATETIME, @StringDate VARCHAR(20)
–Getting date
SET @TodayDate=GETDATE()
— converting @TodayDate to get time time part
SET @StringDate=CONVERT(VARCHAR(10),@TodayDate,108)
–Printing today variable
SELECT @TodayDate
–printing the conversion
SELECT @StringDate
–printing the return of MONTH() from a complete date variable
SELECT MONTH(@TodayDate)
–printint the return of MONTH() from time part variable
–SQL interprets the time portion. If the date contains only a time part, the return value is 1, the base month.
SELECT MONTH(convert(DATETIME, @StringDate,103))
–regards,
—-Mauricio Jarquin (Nicaragua) [email removed]
Month function get date as a value, in this case it assuming it is a time part only thus it returning base month.