A couple of weeks back, I ran a contest with MONTH () function and had close to 300 of you answer the puzzle. It was a great brain teaser and we had an awesome response from you. Most of you got the answer right and it is great to see you folks getting the basics right.
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
The first query will return Year = 1 and Month = 1
The second query will return Year = 2001 and Month = 1
The values are different because if the Year is not formatted as YYYY then SQL Server starts from the year 2000 as part of Y2K fix.
When we explicitly specify the format string as ‘0001’ or YYYY, the year stored in the variable would be 0001.
But when we just use one of two characters for the year, SQL Server implicitly converts the value and uses a date as per “2 digit year cut off” setting (which has a default value of 2049).
So, if we use any value between 0 to 49, then 2000 to 2049 will be used. If we use values from 50 to 99, then year value would be 1950 to 1999
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-two-digit-year-cutoff-server-configuration-option?view=sql-server-2017
Sandeep
The first queryis ISO 8601 Format, so 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
Hi Pinal,
If the DATE is “0001-1-1” then the MONTH of the date is 1 and YEAR is 1 as well.
Reason:
If the DATE format is YYYY-X-Z => it considers the YYYY as YEAR
X as MONTH
Z as DAY
If the DATE format is YY-X-Z => it considers YY as MONTH ( which should be less than 12 always else it will throw error)
Z as YEAR of the current date century
X as DAY
In same way,
a)When DATE is “0045-4-3” then the MONTH of the date is 4 and YEAR is 45.
b)When DATE is “11-4-3” then the MONTH of the date is 11 and YEAR is 2003.
following the same logic mentioned above.
Regards,
M.Kirouthiga
Query-1
DECLARE @dt DATE = ‘0001-1-1’
SELECT MONTH(@dt), YEAR(@dt)
GO
— Answer: 1,1
In Above query, answer is printed as 1,1 because when string value passed in to the “@dt” variable is parsed in “YYYY-MM-DD” format. This is what SQL engine does in background. SQL engine tries to match the string format with all predefined formats allowed in SQL-Engine for specific collation.
YEAR function accepts date as an input, if input is a string data-type then it will implicitly convert the string value to date/datetime/datetmie 2 (whichever best fits) and apply the function on that.
So in above query, as mentioned, it will parse the date as “0001 Jan 1st” and returns “1” for the year. Which makes sense !!
Query-2
DECLARE @dt DATE = ‘1-1-1’
SELECT MONTH(@dt), YEAR(@dt)
— Answer: 1,2001
In above query when we are giving string value ‘1-1-1′ to date variable it parses the value in “MM-DD-YY” format (Again this is decided by SQL Server Engine). So per syntax YY tends to be “01”. So its year value will be “2001”. Why ?? This is how “MM-DD-YY” format works … Whatever you put in “YY” is prefixed with “20”. This is how its been seen in almost all programming languages. And this is the reason now its giving us “2001” in the result for year function.
Few more examples:
select DATEPART(YEAR,’1-1-0′) — Output: 2000
select DATEPART(YEAR,’1-1-1′) — Output: 2001
select DATEPART(YEAR,’1-1-10’) — Output: 2010
I hope above explanation helps understanding why both queries producing different results for YEAR function.
Currently I don`t think if we can change SQL-Server Engine`s tendency to prefix “YY” value with anything else other than “20” when using “MM-DD-YY” format. If any, please share your view on it.
Thanks Pinal Sir !!
-Chirag
SQL Server tendency is not in that way.
select year(‘1-1-50’) – this will again give 1950 as year.
There is some logic behind this. Sorry am also not sure
By default, the date format for SQL server is in U.S. date format MM/DD/YY, unless a localized version of SQL Server has been installed. This setting is fine for cases when an application that requires this functionality is deployed in a manner guaranteeing that dates are used and inserted in the same format across all platforms and locations where the application is used.
Query-1
DECLARE @dt DATE = ‘0001-1-1’
SELECT MONTH(@dt), YEAR(@dt)
GO
— Answer: 1,1
In Above query, answer is printed as 1,1 because when string value passed in to the “@dt” variable is parsed in “YYYY-MM-DD” format. This is what SQL engine does in background. SQL engine tries to match the string format with all predefined formats allowed in SQL-Engine for specific collation.
YEAR function accepts date as an input, if input is a string data-type then it will implicitly convert the string value to date/datetime/datetmie 2 (whichever best fits) and apply the function on that.
So in above query, as mentioned, it will parse the date as “0001 Jan 1st” and returns “1” for the year. Which makes sense !!
Query-2
DECLARE @dt DATE = ‘1-1-1’
SELECT MONTH(@dt), YEAR(@dt)
— Answer: 1,2001
In above query when we are giving string value ‘1-1-1′ to date variable it parses the value in “MM-DD-YY” format (Again this is decided by SQL Server Engine). So per syntax YY tends to be “01”. So its year value will be “2001”. Why ?? This is how “MM-DD-YY” format works … Whatever you put in “YY” is prefixed with “20”. This is how its been seen in almost all programming languages. And this is the reason now its giving us “2001” in the result for year function.
Few more examplpes:
select DATEPART(YEAR,’1-1-0′) — Output: 2000
select DATEPART(YEAR,’1-1-1′) — Output: 2001
select DATEPART(YEAR,’1-1-10′) — Output: 2010
In Addition to that it currently adds prefix “20” to “YY” value only if year is less than “50”. As soon as “50” is passed as “YY” value in “MM-DD-YY” formatted string, it will will prefix “19” to “YY”(year) value.
select DATEPART(YEAR,’1-1-50’) — 1950
I hope above explanation helps understanding why both queries producing different results.
Thank You !!
-Chirag
Query 1: Result will be –> 1,1
Query 2: Result is –> 1 , 2001
and the reason is, Since we have mentioned the year part in 1 digit, it takes the year values in
1950-2049 range.This is the default time span for a 2 digit/ 1 digit year, in SQL Server(two digit year cutoff)
For Query 1, as we have mentioned the whole 4 digits in year part , it takes the year value as it is.
DECLARE @dt DATE = ‘0001-1-1’
SELECT MONTH(@dt), YEAR(@dt)
@dt treat as (‘yyyy-m-d’)
So it shows Result 1 and 1
DECLARE @dt DATE = ‘1-1-1’
SELECT MONTH(@dt), YEAR(@dt)
@dt treat as (‘m-d-y’) for ‘1-1-49’ it shows 2049 while ‘1-1-50’ it shows 1950
Hi Pinal,
For date default string later format is YYYY-MM-DD
Hence output of year is 2001
Declare @dt date = ‘1-1-1’
Select Month(dt), Year(dt)
Output
1 2001
Hi Pinal,
I am getting Error For Query1
Msg 134, Level 15, State 1, Line 3
The variable name ‘@dt’ has already been declared. Variable names must be unique within a query batch or stored procedure.
Hi Pinal
small correction error was rectified
Query1:
The output of Query1 is Month=1 ; Year=1
Here, the string literal passed to the date variable @dt is valid…
The DATE data type range starts from 0001-01-01 through 9999-12-31. The given date is valid, so the output results as usual Month and Year from the given date…..
Query2:
The DATE type supports only four-digit or two-digit years. SQL Server interprets two-digit year as four-digit year using “two digit year cutoff” option value ( Object Explorer –> Right-Click on Server –> Properties –> Advanced –> Miscellaneous –> Two Digit Year Cutoff ).
By default the value of Two Digit Year Cutoff is 2049.
A two-digit year that is less than or equal to the last two digits of the cutoff year is in the same century as the cutoff year. A two-digit year that is greater than the last two digits of the cutoff year is in the century that precedes the cutoff year.
For example, if two digit year cutoff is 2049 (the default), the two-digit year 49 is interpreted as 2049 and the two-digit year 50 is interpreted as 1950.
In the same way, 1 is interpreted as 2001..
DECLARE @dt DATE = ‘1-1-1’
SELECT @dt — 2001-01-01
SELECT MONTH(@dt), YEAR(@dt)
output:
Month = 1; Year=2001
Year part depends on the Two Digit Year Cutoff setting…
–If the Two Digit Year Cutoff is 2049
DECLARE @dt DATE = ‘1-1-1’
SELECT MONTH(@dt), YEAR(@dt) — 2001
GO
–If the Two Digit Year Cutoff is 9999
DECLARE @dt DATE = ‘1-1-1’
SELECT MONTH(@dt), YEAR(@dt) — 9901
GO
–If the Two Digit Year Cutoff is 1750
DECLARE @dt DATE = ‘1-1-1’
SELECT MONTH(@dt), YEAR(@dt) — 1701
I’m try to answer this..sorry if my english so bad.. :D
DECLARE @dt DATE = ‘0001-1-1’
SELECT MONTH(@dt), YEAR(@dt)
Output Query :
month year
1 1
‘0001-1-1’ = YYYY-MM-DD
YEAR ( date )
Return type is integer
so 0001 become 1
DECLARE @dt DATE = ‘1-1-1’
SELECT MONTH(@dt), YEAR(@dt)
Output Query :
month year
1 2001
‘1-1-1’ = MM-DD-YYYY
1 become 2001 because two digit year cutoff Server Configuration Option. The default time span for SQL Server is 1950-2049.
SELECT YEAR(‘1-2-1’)
month year
1 2001
SELECT YEAR(‘1-2-49’)
month year
1 2049
SELECT YEAR(‘1-2-50’)
month year
1 1950
At the first query the date format is ‘yyyy-M-d’ so the sql takes the whole number that refers to the year part which is “0001” and consider it as a year so it returns 1.
At the second query the date format is ‘M-d-y’ , so the sql takes the number that refers to the year which is “1” and transfer it to the equivalent year so it return 2001.
Hi Pinal,
Query 1 result is obvious. The date format is YYYY-MM-DD. So the year is ‘0001’ and YEAR() function returns 1;
For query 2, two-digit year cutoff will take effect. The default two-digit year cutoff year is 2049.
Since the year assigned to the @dt variable is 1 and that is less than the last two digit of cutoff year, it falls in the same century of 2000 and +1. This results in the year 2001. If the year assigned was ’50’, then the function returns ‘1950’ ( 50 + 1900 [preceding century of 2049]).
Thanks for the interesting question!
The Year() function for both the queries returns different value:
First Query => 0001
Second Query => 2001
This happens because of the “Two Digit Year Cut-off” property setting of SQL server.
1. Since first query above supplied a 4 digit value for year part of the date hence remained intact and SQL didn’t apply the two digit year rule on the value
2. However in the second case, since the year value is passed as 2 digit year hence the “Two Digit Year Cut-off” property came into effect and converted the year to 2000 series basic the range 1950-2049.
Hope this clarifies!!!
Also, what is the result status for the previous Month() function quiz?
Hi Pinal,
The values are different because the year ‘YYYY’ can be specified either at the end or it may be at the beginning in a Date Format
Eg : ‘1900-01-01′ or ’01-01-1900’
If we Specify 4 digits in the beginning of the date ‘1900-01-01’ it will assume first part as year and it will display 1900 as year.
As Same the Query 1 has ‘0001’ in the beginning and SQL server assumes 0001 as year and returns 1.
In case if we doesn’t specify any year part (4 Digits) the SQL Server assumes last part as year (By default if we specify 00 at the end it will assume 2000 as year until 49 and from 50 it will assumes as 1950 up to 99 and it will throw error if specify above 99).
The Second Query belongs to this case as it assumes year as ‘2001-01-01’ and returned 2001 as year.
(No column name) (No column name)
1 2001
A two-digit year that is less than or equal to the last two digits of the cutoff year is in the same century as the cutoff year. A two-digit year that is greater than the last two digits of the cutoff year is in the century that comes before the cutoff year. For example, if the two-digit year cutoff is the default 2049, the two-digit year 49 is interpreted as 2049 and the two-digit year 50 is interpreted as 1950.
This is because of two year cutoff server configuration of SQL Server. Its default span is 1950-2049. Also the preceding 0 (zero) has no meaning for integer values. Hence the reason Query 1 returns 1 as the year i.e. it’s of four digit and Query 2 fall in two digit year cutoff configuration hence returns 2001 as the result.