finding the start day (Monday) of the current week

问题:

Looking for a SQL query/queries that would determine the start day (Monday) of the current week.

Example: If today is -> then the start of the week is

Sat Oct 09, 2010 -> Start of the week is Monday Oct 04, 2010
Sun Oct 10, 2010 -> Start of the week is Monday Oct 04, 2010
Mon Oct 11, 2010 -> Start of the week is Monday Oct 11, 2010
Tue Oct 12, 2010 -> Start of the week is Monday Oct 11, 2010

I have seen many "solutions" on Google and StackOverflow. The look something like:

SET @pInputDate = CONVERT(VARCHAR(10), @pInputDate, 111)
SELECT DATEADD(DD, 1 - DATEPART(DW, @pInputDate), @pInputDate)

This fails because: Sun Oct 10, 2010 -> start of week Monday Oct 11, 2010 (which is incorrect).

回答1:

Try using DATEFIRST to explicitly set the day of week to be regarded as the 'first'.

set DATEFIRST 1  --Monday
select DATEADD(DD, 1 - DATEPART(DW, @pInputDate), @pInputDate)

This will return the Monday of the week the InputDate falls in.

回答2:

Building on top of p.campbell's solution, if you don't want to use or can't use "SET DATEFIRST 1", you can get around that by doing the following:

SELECT DATEADD(DD, 2 - DATEPART(DW, DATEADD(DD, -1, @pInputDate)), DATEADD(DD, -1, @pInputDate))