Previous Monday & Previous Sundays date based on t

问题:

I need the correct syntax to give me :

1. Previous weeks Mondays date based on the current date/time using GETDATE()
2. Previous weeks Sundays date based on the current date/time using GETDATE()

So, based on todays date (14/09/2012) I would want the following:

1. Previous Mondays date = 03/09/2012
2. Previous Sundays date = 09/09/2012

回答1:

Easy:

--start of last week
SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 0)

--end of last week
SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6)

EDIT:

The below will handle the Sunday date issue.

DECLARE @input varchar(10)
--SET @input = '9/9/2012' -- simulates a Sunday
SET @input = GETDATE()

--start of last week
SELECT DATEADD(wk, DATEDIFF(wk, 6, 
CASE DATEPART(dw,@input)
WHEN 1 THEN DATEADD(d,-1,@input)
ELSE @input
END
), 0)

--end of last week
SELECT DATEADD(wk, DATEDIFF(wk, 6, 
CASE DATEPART(dw,@input)
WHEN 1 THEN DATEADD(d,-1,@input)
ELSE @input
END
), 6)

回答2:

Instead of using a case option, you could also do this to get the current week's Sunday:

SELECT DATEADD(dd, DATEPART(DW,GETDATE())*-1+1, GETDATE())

To get the previous week's Sunday, subtract 7 more days:

SELECT DATEADD(dd, DATEPART(DW,GETDATE())*-1-6, GETDATE())

回答3:

I think this is much cleaner solution:

SELECT
    -- 17530101 or 1753-01-01 is the minimum date in SQL Server
    DATEADD(dd, ((DATEDIFF(dd, '17530101', GETDATE()) / 7) * 7) - 7, '17530101') AS [LowerLimit], -- Last Week's Monday
    DATEADD(dd, ((DATEDIFF(dd, '17530101', GETDATE()) / 7) * 7) - 1, '17530101') AS [UpperLimit] -- Last Week's Sunday.

Which can be used like this in a real world query:

SELECT
    *
FROM
    SomeTable
WHERE
    SomeTable.[Date] >= DATEADD(dd, ((DATEDIFF(dd, '17530101', GETDATE()) / 7) * 7) - 7, '17530101') AND
    SomeTable.[Date] <= DATEADD(dd, ((DATEDIFF(dd, '17530101', GETDATE()) / 7) * 7) - 1, '17530101')

Here are some tests:

1. Leap Year

Current Date: 2016-02-29 00:00:00.000

Results:

LowerLimit                 UpperLimit
2016-02-22 00:00:00.000    2016-02-28 00:00:00.000

2. Last Week was in different year

Current Date: 2016-01-06 00:00:00.000

LowerLimit                 UpperLimit
2015-12-28 00:00:00.000    2016-01-03 00:00:00.000

3. Lower limit in previous month and upper limit in current month

Current Date: 2016-05-04 00:00:00.000

LowerLimit                 UpperLimit
2016-04-25 00:00:00.000    2016-05-01 00:00:00.000

4. Current Date is Sunday

Current Date: 2016-05-08 00:00:00.000

LowerLimit                 UpperLimit
2016-04-25 00:00:00.000    2016-05-01 00:00:00.000

回答4:

Even better, I think, this works for any date, any week day, with any DateFirst parameter (set the first day of the week, generally 1-Monday in France, default is 7-Sunday).

create function [dbo].[previousWeekDayDate](@anyDate date, @anyWeekDay int)
returns Date
as
begin
    return DATEADD(dd, ((DATEPART(dw,@anyDate) + @@DateFirst - @anyWeekDay + 13) % 7) * -1, @anyDate)
end

works for SQL 2008, create the function and use:

SELECT dbo.previousWeekDayDate(GetDate(),1) --for Monday
SELECT dbo.previousWeekDayDate(GetDate(),7) --for Sunday

回答5:

It should be noted that the issue with Sundays appears to no longer be present at least as of MSSQL 2012. Both the simple solution

SELECT DATEADD(wk, DATEDIFF(wk, 6, @input), 0)

and the complex one

SELECT DATEADD(wk, DATEDIFF(wk, 6, 
CASE DATEPART(dw,@input)
WHEN 1 THEN DATEADD(d,-1,@input)
ELSE @input
END
), 0)

return the same for any date that I've tried, including Sundays.