As a SQL Server enthusiast, I often come across interesting questions related to SQL Server during my interactions with the community. Recently, a friend of mine shared an interview question that caught my attention. The question was about efficiently finding the number of days in a given year. While this may seem like a simple task, it intrigued me to explore different approaches to solve it.

Initially, my instinctive approach was to use basic SQL Server functions like DATEADD and DATEPART. Here’s an example of how I solved it:

-- This method works on SQL Server 2005 and above.
DECLARE @year AS INT
SET @year = 2012

SELECT DATEPART(dy, (DATEADD(YEAR, @year - 1899, 0) - 1)) AS [TOTAL NO OF DAYS]

After sharing this solution, my friend challenged me to utilize some of the new datetime functions introduced in SQL Server 2012. This caught me off guard, but I was determined to find a solution using these new functions. After some research, I came up with the following query:

-- This technique works in SQL Server 2012 and above.
DECLARE @year AS INT
SET @year = 2012

SELECT DATEPART(dy, DATEFROMPARTS(@Year, 12, 31)) AS [TOTAL NO OF DAYS]

Wow! It was not as simple as I had initially thought. I had to dig into my blog and explore different ideas to come up with this solution. However, the challenge didn’t end there. My friend further asked if I could find a solution with fewer functions involved. This made me ponder and explore alternative approaches.

So, here’s a quiz for you: Can you come up with a solution that involves even fewer functions? Can you leverage some of the new SQL Server 2012 Date functions in your solution? I would love to hear your thoughts and solutions in the comments section below.

Stay tuned for the next blog post where I will share my optimized solution and discuss the different approaches to efficiently find the number of days in a given year using SQL Server.