SQL SERVER – Puzzle with Year Function

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'

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'

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)

SQL DateTime, SQL Scripts, SQL Server
Previous Post
SQL SERVER – Know Your Backup Before Deleting Database
Next Post
SQL SERVER – Security Auditing With ApexSQL Audit

Related Posts

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…?


  • 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


Leave a ReplyCancel reply

Exit mobile version