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.


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.


About Dattatrey Sindol (Datta)

Datta is a Microsoft BI Enthusiast, passionate developer, and a blogger. View Full Profile

Posted on August 20, 2010, in Code Snippets, SQL Server, T-SQL, T-SQL Snippets. Bookmark the permalink. Leave a comment.

What are your thoughts?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s