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
SELECT CAST(‘0001-1-1’ AS DATE) –> 0001-01-01. Hence “MONTH” function on this will return 1 and “YEAR” function will also return 1.
SELECT CAST(‘1-1-1’ AS DATE) –> 2001-01-01. Hence “MONTH” function on this will return 1 and “YEAR” function will return 2001.
Oh, that’s a nice one. So many things to talk about… ok, so the first query returns exactly what is expected: year 1 and month 1, since SQL Server accurately casts the text to the 01/01/0001 date based on date style 120 (ODBC Canonical).
However, on the second query, SQL Server will cast the date based on style 1 (U.S. without century – mm/dd/yy). If we lookup in MSDN, it will state that date styles without century have 2049 as its cutoff year. Therefore, the string ‘1-1-1’ is actually being converted to 01/01/2001, and so the query returns year 2001 and month 1.
Even more interesting is the fact that, thanks to the cutoff (which can actually be configured), if you were to use the string ‘1-1-50’ it will convert to 01/01/1950. This just goes to show that using the styles without century can be very dangerous as you could suddenly be back in the 20th century depending on the year value supplied.
Query 1: By default date pattern is “YYYY-MM-DD” that ranges from ” 0001-01-01 through 9999-12-31.
hence YEAR(@dt) = 1
Query 2: ‘1-1-1’ will be considered as ‘mm-dd-yy’ notation. yy value 1 to 49 will be cutt off year from 2001 to 2049, and 50 to 99 will be 1950 to 1999.
DECLARE @dt DATE = ‘1-1-1’ is equal to DECLARE @dt DATE = ‘1-1-01’
Hence YEAR(@dt) = 2001
Query 1 was taken as yyyy-mm-dd and Query 2 was taken as mdy (the default format – mm/dd/yy). So year was taken as 01. we represent 01 as 2001 from 20th century. It would be the same case if we use ‘1-1-2’. In this case year would be 2002
This is because of two digit formatting,
SQL server default format for date is ‘yyyy-MM-dd’, So it’s take as it is and if specify 2 or less digits it’s take as current year prefix 20 or 200,
year
‘0001’ as 1
and
‘1’ as 2001
you parse 1,2,4 digit only for year, if 3 digit year get an conversion error, if it sees 4 digits at beginning or last, it takes it as year
Query 1..Month–>1
Year—>1
Reason–>It takes YYYY-MM-DD format
Query 2..Month–>1
Year—>2001
Reason—>it takes system date format
In 1st query , its clearly year written as 0001, while in second query it will consider current century and return 2001
In first query, its clearly written year as 0001, while in second query because single digit 1 is given as year, its taking current century and return 2001
For the DATE datatype in SQL server default date span is 1950-2049. so if we use year in single or double digit it will create date using this SPAN date frame.
E.g. for our example Query 2: date is ‘1-1-1’. We have year as 1 and it is less then 49(higher year cutoff of default Span) so it will consider current century to generate date,so our date will be (2001-1-1). And in case we have year cutoff for date (1-1-70) greater or equal to 50(lower year cutoff of default Span) it will treat it for previous century,so date will be 1970-1-1.
Also for Query 1 we have clearly mentioned year as “0001” so it will take as it is.
hi,
when I run below Query 1:
DECLARE @dt DATE = ‘0001-1-1’
SELECT MONTH(@dt), YEAR(@dt)
then I got result year 1 because hear year format is yyyy and I have put “0001” year in query
here mention that year is 0001 means 1 (format is yyyy-mm-dd)
output is
Month 1 and year 1
and when I run below Query 2:
DECLARE @dt DATE = ‘1-1-1’
SELECT MONTH(@dt), YEAR(@dt)
then I got result year 2001 because here year format is yy so when value is 1 then database will consider 2001 ( year of current century)
and if we put year value is 51 then year output is 1951 that time it consider previous century 1900
so when year value is 0 to 49 that time database will consider century(2000) and when year value is 50 to 99 that time database will consider previous century (1900)
I hope it is clear
Thanks
Hardik rawal
DECLARE @dt DATE = ‘0001-1-1’
SELECT MONTH(@dt), YEAR(@dt)
The above input of @dt is in yyyy/mm/dd format. Hence it gives 1 as year.
The below is in default mm/dd/yy format, so it’s giving 2001.
DECLARE @dt DATE = ‘1-1-1’
SELECT MONTH(@dt), YEAR(@dt)
If I set @dt = ‘1-1-50’ then I will get 1950 as Year and if I set @dt = ‘1-1-49’ then I will get 2049 as Year. This is because of two digit year cutoff.
Best Regards,
Arbind Chandra
The cause of the difference is the number of digits specified for the year value. When 4 digits are specified, there’s no need to interpret the year value. However, when 2 or less digits are specified, SQL Server places values between 00 and 49 into the current century, and values from 50 to 99 into the previous century. With this in mind, one might be tempted to place try the text values ’49-1-1′ and ’50-1-1′ to try this out, but that would cause SQL Server to no longer recognize the value as a valid date. However, try ‘1-1-49’ and ‘1-1-50’ and you’ll see that the former shows up in the year 2049, while the latter in the year 1950.
Hi,
Query: 1
DECLARE @dt DATE = ‘0001-1-1’
SELECT MONTH(@dt) as [Month], YEAR(@dt) as [Year]
SELECT CAST(‘0001-1-1’ as Date) => 0001-01-01
If we pass full year (yyyy), sql returns the same year so the answer for the year function is 0001.
Output:
Month Year
1 1
———————————————————————————-
Query: 2
DECLARE @dt DATE = ‘1-1-1’
SELECT MONTH(@dt) as [Month], YEAR(@dt) as [Year]
SELECT CAST(‘1-1-1’ as Date) => 2001-01-01
This is happens in SQL this is “Century colloquially”.
If we pass year in either y or yy format then sql trying to convert into full year based on below rules.
Rule 1: If the current date falls in first half of the century (means years between 1 to 49)
a. If you pass a date in the first half of the century (i.e. from 1 through 49), SQL returns the current century.
b. If you pass a date in the second half of the century (years between 50 to 99), SQL returns the previous century.
Rule 2: If the current date falls in the second half of the century (years from 50 to 99)
a. If you pass a date in the first half of the century, SQL returns the next century.
b. If you pass a date in the second half of the century, SQL returns the current century.
Answer for Query 2:
We are passing date as 1-1-1, currently we are (2015) in first half of the 21st century (2000 to 2049) so Rule #1 belongs. and we passing date (here year is 1) falls in first half of the century (1 to 49), so Rule #1 a. is applicable, so starting year of an current century (2000). passing year is 1, Finally we got 2000+1=2001 is an year part. That is the reason we getting date as (2001-01-01)
SELECT CAST(‘1-1-1’ as Date) => 2001-01-01
SELECT YEAR(CAST(‘1-1-1’ as Date)) => 2001.
Output:
Month Year
1 2001
Thanks,
Selvakumar S
DECLARE @dt DATE = ‘0001-1-1’
SELECT MONTH(@dt), YEAR(@dt)
Out Put
given input format ‘yyyy-mm-dd’
if year four digit than convert that four digit value to int result 1
Month =1
Year =1
DECLARE @dt DATE = ‘1-1-1’
SELECT MONTH(@dt), YEAR(@dt)
given input single digit than
sql server date start from 1900-01-01
single digit convert to two digit before add zero
‘1-1-01’ as ‘dd-mm-yy’
if two digit year vale less than 49
add 2000+49 =2049
if two digit year vale greater than or equal to 50
add 1900+49 =1949
than Output Month -1
Year -2001
For Query1:
What is the output for the below query? And why?
DECLARE @dt DATE = ‘0001-1-1’
SELECT MONTH(@dt), YEAR(@dt)
The output of above query will be 1 as year value is passed as ‘0001’ and YEAR() returns int value only
For Query2:
DECLARE @dt DATE = ‘1-1-1’
SELECT MONTH(@dt), YEAR(@dt)
The output of above query is 2001 as The argument for date is the number which cannot interprets by function so it takes it as January 1.2000 by Y2K effect and then giving the answer as 2000+1 =2001 as year value here
if you pass ‘1-1-0’ then it will display the value of YEAR function as 2000
DECLARE @dt DATE = ‘1-1-1’
SELECT MONTH(@dt), YEAR(@dt)
Answer Is
In this query
Fisrt getyear is 1 but output is 2001
Step 1
check wheather length of year
if years length is 3
Error
else
getyear value (@dt DATE = ‘1-1-1’)
Step 2
Like getyear(getdate) from getdate() and take a value from left on first digit as ‘2’
Step 3
from step2 getvalue is ‘2’
from step1 getvalue is ‘1’
But in Year total value is 4
so remaining need values = 2 so we add a values default is 0
concat(‘2′,’0′,’0′,1’)
Step 4
But in getyear(‘1991’) mean that output is 1001
This is my point of answer
DECLARE @dt1 DATE = ‘5-17-16’
SELECT @dt1, MONTH(@dt1), YEAR(@dt1)
2016-05-17 5 2016
So output is 2016
Year function of SQL take DateTime or Date type as parameter. DateTime format of SQL is ‘yyyy-mm-dd’ so Ir-respective to value format Year function consider passed value in ‘yyyy-mm-dd’
So here in first query ‘0001-1-1’ is in ‘yyyy-m-d’ format,hence output will be 1,1.
For second query ‘1-1-1’,SQL Engine convert value to default format so output will be ‘2001-01-01’ [please refer https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-two-digit-year-cutoff-server-configuration-option?view=sql-server-2017 for more detail] . And hence this query will return output as 1,2001.
If you see in the Advance Properties in Sql Editor, you can see Two digit Year cutoff as 2049, it is default max date, if you need 2050 just edit it to 2050.