When I was on that step, I was wondering to run this second contest. Sometimes our basics can get tricked if we add a twist to the whole setup. Below are two sets of Queries and I would want you to guess what is the output and why?
Query 1:
What is the output for the below query? And why?
DECLARE @dt DATE = '0001-1-1' SELECT MONTH(@dt), YEAR(@dt)
Part of the answer is in the earlier puzzle I mentioned starting this blog. But what would the year value show? Take a guess and let me know.
Query 2:
I have made a small change in the default value for the second query. Now can you guess what the output from this query is?
DECLARE @dt DATE = '1-1-1' SELECT MONTH(@dt), YEAR(@dt)
As part of the hint I can tell you the MONTH function returns the same value. But there is something different for the YEAR function.
So here is the quiz – Why are the values different? What is the reason behind this?
Please leave correct answer in comment below the blog.
I will announce winner of this contest in 48 hours. Two people who give the right answer, I will share a learning resource worth USD 29 on June 11 via Email.
Reference: Pinal Dave (https://blog.sqlauthority.com)
46 Comments. Leave new
When we take Date as ‘0001-1-1’
The format of date applied is YYYY-MM-DD, which considers 0001 as YYYY and hence returns Year = 1.
But, when we take Date as ‘1-1-1’
The format of date applied here is not YYYY-MM-DD but DD-MM-YY.
Now here, if you use only 2 digits in Year, It assumes it to be the date of year ‘2000’, where first 2 digits ’20’ represents year ‘2000’ followed by the last 2 digits provided in Date string.
Hence, we get Year = ‘2001’ as output.
Can you post the winner names here…?
Reagrds,
Chandrika
In the first query format of the date is yyyy-mm-dd format always gives the year part as yyyy will exclude the zeros and provide the output.
DECLARE @dt DATE = ‘0001-1-1’
SELECT MONTH(@dt), YEAR(@dt)
Answer is 0001 = 1, 0002 = 2.
The second query is Numeric Date Formats of type mdy.
In this case y is concatenated with 19 until 49 and after 20 meaning ‘1-1-50’ to ‘1-1-99′ is concatenated with 19 i.e ’19’+’50’ to ’19’+’99’.
From 0 to 49 it is concatenated with 200 or 20 i.e ‘200’+’0′ to ’20’+’49’.
DECLARE @dt DATE = ‘1-1-1’
SELECT MONTH(@dt), YEAR(@dt)
The above output is concatenated with 200+1