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:

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

Readers Feedback:

Did you like this article? If Yes, then please share it: | More Options

Did you like my blog? If Yes, then please help spread the word by sharing this blog with your colleagues, friends, & anyone else in the MSBI Space: Like us on Facebook , Follow us on Twitter .