Query to Get Week Start Date, Month Start Date and Year Start Date Based on Input Date

In one of the recent projects, there was a requirement to set the First Day of the Week as Start Date and Current Date as End Date for a Week Level Report. Similarly, First Day of Month & Current Date and First Day of Year & Current Date for Month Level and Year Level Reports respectively.

To address this requirement, one of the developers wrote a query to get the first day of the week considering the week starts on a Monday and the query seemed to work fine on his computer but when we moved that code to my computer then the results were different & were completely incorrect. When I started looking into the issue I found that he was using DATEPART function of SQL Server something like DATEPART(DW, @InputDate). After doing some analysis & referring to the properties / behavior of this function on MSDN library, I found that the return value of this function depends on the setting of DATEFIRST.

One can find out the current state of DATEFIRST property by running the following simple SELECT statement.

SELECT @@DATEFIRST 

Refer to the DATEPART() function on MSDN library for more details.

Following is the query which gives accurate results under all circumstances:

DECLARE @dttRequestedDateTime DATETIME = '2010-08-20' -- Replace '2010-08-20' with Input Date or Requested Date 
DECLARE @dtInputDate DATE = CAST(@dttRequestedDateTime AS DATE)
 
SELECT  
      @dtInputDate AS [InputDate],
      CASE
         WHEN DATENAME(DW, @dtInputDate) = 'Monday' THEN @dtInputDate
         WHEN DATENAME(DW, @dtInputDate) = 'Tuesday' THEN DATEADD(DD, -1, @dtInputDate)
         WHEN DATENAME(DW, @dtInputDate) = 'Wednesday' THEN DATEADD(DD, -2, @dtInputDate)
         WHEN DATENAME(DW, @dtInputDate) = 'Thursday' THEN DATEADD(DD, -3, @dtInputDate)
         WHEN DATENAME(DW, @dtInputDate) = 'Friday' THEN DATEADD(DD, -4, @dtInputDate)
         WHEN DATENAME(DW, @dtInputDate) = 'Saturday' THEN DATEADD(DD, -5, @dtInputDate)
         WHEN DATENAME(DW, @dtInputDate) = 'Sunday' THEN DATEADD(DD, -6, @dtInputDate)
      END AS [WeekStartDate] 
      , DATEADD(DD, 1 - DATEPART(DAY, @dtInputDate), @dtInputDate) AS [MonthStartDate] 
      , DATEADD(DD, 1 - DATEPART(DAYOFYEAR, @dtInputDate), @dtInputDate) AS [YearStartDate]

Here is the output of this query:

InputDate  WeekStartDate MonthStartDate YearStartDate
---------- ------------- -------------- -------------
2010-08-20 2010-08-16    2010-08-01     2010-01-01

(1 row(s) affected)

Week Start Date, Month Start Date & Year Start Date

Let me know if you have a better way of getting the same results by leaving a comment below.

Related Articles and Links

• T-SQL - Coding Standards & Best Practices
• T-SQL Coding Made Easy with Inbuilt SQL Server Templates
• Query to Select a Random Record from a Table in SQL Server