SQL 查询周开始结束日期

1. DECLARE @dbDefaultWeekStart INTEGER = @@DATEFIRST; /*take backup of db default week start day*/
2. DECLARE @expectedWeekStart INTEGER = 6; /*expected week start from 'Saturday'*/
3. SET DATEFIRST @expectedWeekStart; /*set week start day as expected*/
5. /*doing our calculation as needed*/
6. DECLARE @dateTimeNow DATETIME = GETDATE();
7. SELECT
8. [StartDate] = DATEADD(dd, -(DATEPART(WEEKDAY, @dateTimeNow)-1),
   
9. DATEADD(dd, DATEDIFF(dd, 0, @dateTimeNow), 0)),
10. [EndDate] = DATEADD(dd, 7-(DATEPART(WEEKDAY, @dateTimeNow)),
    
11. DATEADD(dd, DATEDIFF(dd, 0, @dateTimeNow), 0));
12. SELECT
13. [StartDateTime] = DATEADD(DAY, -(DATEPART(WEEKDAY, @dateTimeNow)-1),
    
14. DATEADD(DAY, DATEDIFF(DAY, 0, @dateTimeNow), 0)),
15. [EndDateTime] = DATEADD(DAY, 7-(DATEPART(WEEKDAY, @dateTimeNow)),
    
16. DATEADD(SECOND, -1, DATEADD(DAY, DATEDIFF(DAY, 0, @dateTimeNow) + 1, 0)))
18. /*reset week start date to its default or as it was*/
19. SET DATEFIRST @dbDefaultWeekStart;