Calculating Previous Monday and Sunday Dates in T-SQL: An In-Depth Analysis of Date Computations and Boundary Handling

Introduction

In database applications, it is often necessary to calculate specific date ranges based on the current date, such as determining the start and end dates of the previous week. This article focuses on implementing this functionality in SQL Server using T-SQL, with particular attention to accurately calculating the previous Monday and Sunday.

Core Problem Analysis

The user requirement is clear: based on the current date (example: September 14, 2012), calculate:

1. Previous Monday's date: September 3, 2012
2. Previous Sunday's date: September 9, 2012

This involves two key computations: identifying the current week and then rolling back to the previous week, accurately pinpointing its Monday and Sunday.

Review of Fundamental Date Functions

SQL Server offers a rich set of date and time functions, with the following being most relevant to this discussion:

• GETDATE(): Returns the current system date and time
• DATEDIFF(datepart, startdate, enddate): Calculates the difference between two dates
• DATEADD(datepart, number, date): Adds or subtracts a specified time interval to a date
• DATEPART(datepart, date): Returns a specified part of a date (e.g., day of the week)

Analysis of Best Practice Solution

Based on the top-rated answer (score: 10.0) from the Q&A data, the core solution is as follows:

Basic Calculation Logic

Formula for calculating the start of last week (Monday):

SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 0)

Formula for calculating the end of last week (Sunday):

SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6)

Logical Breakdown

1. DATEDIFF(wk, 6, GETDATE()): Calculates the number of week differences between January 6, 1900 (a Saturday) and the current date. January 6, 1900 is chosen as a reference point because it is a Saturday, helping to align week starts.
2. DATEADD(wk, ..., 0): Adds the calculated week difference to the base date (January 1, 1900, a Monday) to obtain the Monday of the current week.
3. By adjusting the second parameter (0 or 6), the dates for Monday or Sunday can be derived respectively.

Boundary Case Handling

The original approach has a potential issue: when the current date is a Sunday, calculations may produce incorrect results. The best answer addresses this with:

DECLARE @input varchar(10)
SET @input = GETDATE()

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

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

Key improvements here:

• Uses DATEPART(dw, @input) to get the day of the week for the current date (1 for Sunday, 2 for Monday, etc.)
• When the current date is Sunday (DATEPART(dw, @input) = 1), rolls back one day to Saturday to avoid week calculation errors
• This handling ensures accurate calculation of the previous week's date range regardless of the current day of the week

Comparison of Alternative Approaches

The Q&A data also includes another method (score: 2.6):

-- Get current week's Sunday
SELECT DATEADD(dd, DATEPART(DW,GETDATE())*-1+1, GETDATE())

-- Get previous week's Sunday
SELECT DATEADD(dd, DATEPART(DW,GETDATE())*-1-6, GETDATE())

This method directly calculates based on the current day of the week:

• DATEPART(DW,GETDATE())*-1+1: Calculates the day difference to the current week's Sunday
• Subtracts 7 days to get the previous week's Sunday

While logically feasible, this approach has limitations:

1. Depends on SET DATEFIRST settings, as week start days may vary by region
2. Does not explicitly handle boundary cases where Sunday is the week start
3. Lower code readability with less intuitive calculation logic

Performance and Maintainability Considerations

In practical applications, beyond functional correctness, it is essential to consider:

Performance Optimization

• Calculations in the best answer are deterministic and can leverage index optimization
• Avoid applying functions to date columns in WHERE clauses, as this can disable index usage
• Consider storing results as computed columns or views to enhance query performance

Code Maintainability

• The best answer uses a clear reference date (January 6, 1900) and week count calculations, making logic transparent
• CASE statements explicitly handle boundary cases, facilitating future maintenance and debugging
• Adding comments to explain the calculation logic, especially the choice of reference date, is recommended

Practical Application Example

Below is a complete stored procedure example for obtaining the previous week's date range:

CREATE PROCEDURE GetLastWeekDates
    @StartDate DATE OUTPUT,
    @EndDate DATE OUTPUT
AS
BEGIN
    DECLARE @CurrentDate DATE = CAST(GETDATE() AS DATE)
    DECLARE @AdjustedDate DATE
    
    -- Adjust for Sunday cases
    IF DATEPART(dw, @CurrentDate) = 1
        SET @AdjustedDate = DATEADD(DAY, -1, @CurrentDate)
    ELSE
        SET @AdjustedDate = @CurrentDate
    
    -- Calculate previous Monday
    SET @StartDate = DATEADD(WEEK, DATEDIFF(WEEK, 6, @AdjustedDate), 0)
    
    -- Calculate previous Sunday
    SET @EndDate = DATEADD(WEEK, DATEDIFF(WEEK, 6, @AdjustedDate), 6)
END

Conclusion

Calculating the previous Monday and Sunday dates is a common date-handling requirement in SQL Server. The best practice solution combines DATEDIFF, DATEADD, and DATEPART functions, ensuring accuracy and robustness through a clear reference date and boundary handling. Developers should select appropriate methods based on specific application scenarios, while also considering performance optimization and code maintainability.