Calculating Week Start and End Dates from Week Numbers in SQL

Problem Background and Requirements Analysis

In database applications, there is frequent need for data statistics and analysis based on week numbers. The original query uses DATEPART(wk, WeddingDate) to obtain week numbers but lacks corresponding week start and end date information. This requirement is common in business reporting, trend analysis, and other scenarios.

Core Solution: Date Offset-Based Calculation

Using DATEPART(dw, WeddingDate) to get the current date's position in the week, combined with DATEADD function for date offset calculation:

SELECT
  SUM(NumberOfBrides) AS [Wedding Count],
  DATEPART(wk, WeddingDate) AS [Week Number],
  DATEPART(year, WeddingDate) AS [Year],
  DATEADD(dd, -(DATEPART(dw, WeddingDate)-1), WeddingDate) AS WeekStart,
  DATEADD(dd, 7-(DATEPART(dw, WeddingDate)), WeddingDate) AS WeekEnd
FROM MemberWeddingDates
GROUP BY DATEPART(year, WeddingDate), DATEPART(wk, WeddingDate)
ORDER BY SUM(NumberOfBrides) DESC

Algorithm Principle Deep Analysis

DATEPART(dw, date) returns an integer from 1 to 7, representing Sunday to Saturday. Week start date calculation is achieved by subtracting (current weekday value - 1) days, adjusting the date to the Sunday of the current week. Week end date calculation adds (7 - current weekday value) days, adjusting the date to the Saturday of the current week.

Time Precision Handling Considerations

In practical applications, the impact of time components must be considered. It is recommended to use CAST(WeddingDate AS DATE) or CONVERT(DATE, WeddingDate) to remove time parts, ensuring accuracy in date calculations.

DATEFIRST-Agnostic Universal Solution

Considering different regional settings for week start days, providing a solution independent of DATEFIRST settings:

SET DATEFIRST 4
DECLARE @d DATETIME
SET @d = GETDATE()

SELECT
  @d AS ThatDate,
  DATEADD(dd, 0 - (@@DATEFIRST + 5 + DATEPART(dw, @d)) % 7, @d) AS Monday,
  DATEADD(dd, 6 - (@@DATEFIRST + 5 + DATEPART(dw, @d)) % 7, @d) AS Sunday

Handling Different Week Definition Standards

The reference article discusses differences between ISO week standards (Monday to Sunday) and US standards (Sunday to Saturday). In practical applications, appropriate week definition methods must be selected based on business requirements, with results adjusted through offsets.

Performance Optimization Recommendations

For large dataset processing, it is recommended to create calendar tables that pre-calculate week start and end dates, using join queries instead of real-time calculations to significantly improve query performance.

Practical Application Scenario Extensions

This method can be extended to various week-based time series analyses, including sales statistics, user behavior analysis, operational metric monitoring, and other scenarios, providing accurate time dimension support for business decisions.