T-SQL generates two new real calendars

DateFirst has not been related 2004-09-29: - Playyuer@microshaoft.com invent - http://www.microshaoft.com declare @ DateTime set @ = '1995-02-25 11: 00: 50'set @ = getdate () --Set Datefirst 3 Select @ as date, dateadd (Year, Datediff (Year, 0, @), 0) as firstddayofyear, dateadd (Year, 1 Datediff (Year, 0, @), 0) -1 AS LastdayoFyear, Dateadd (Quarter, Datediff (Quarter, 0, @), 0) As FirstdayOfquarter, Dateadd (Quarter, 1 Datediff (Quarter, 0, @), 0) -1 AS LastDayofquarter, Dateadd (Month, Datediff (Month, 0, @, 0) As Firstdayofmonth, Dateadd (Month, 1 Datediff (Month, 0, @), 0) -1 AS LastDayofmonth, Dateadd (Week, Datediff (Week, 0, @), 0) As Firstdayofwek, Dateadd (Week, 1 Datediff (Week, 0, @), 0) -1 As LastDayofweek - Sunday Calculated this week (SQL Server default) Select Month (DateAdd (DAY, 0, Datediff (Day, Di, Dateadd (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0))))))))))))))))))))))))))))))))))))))))))))) , Datediff (Day, 0, Dateadd (Year, Datediff (Year, 0, @), 0))), DateAdd (Day, 0, Datediff (Day, 0, DateAdd Dateadd (Year, Datediff (Year, 0, @), 0))))) 1 AS Weekofyear, Datediff (Week, Dateadd (Month, Datediff (Month, 0, MIN) Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))), 0)))), 0), Min (Dateadd (Day, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))) 1 AS Weekofmonth , Case When (@@ Datefirst Datepart (Weekday, Min (Dateadd (DAY, 0, Dateadd (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, 0, @) , 0))))))))))% 7 =

1 THEN min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))) ELSE NULL End As Sunday, Case When (@@ Datefirst Datepart (Weekday, Min (Dateadd (DAY, 0, Dateadd (Day, Di, Dateadd (Month, Mi, DateAdd (Year, 0 , @), 0)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))% 7 = 2 THEN MIN (Dateadd (DAY, 0, Dateadd (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, Datediff) Year, 0, @), 0))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))). Dateadd (Year, Datediff (Year, 0, @))))))) 1))% 7 = 2 and datediff (Dateadd (DAY, 0, Datediff (day, 0, dateadd , Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))))))))))), Min (Dateadd (Day, 0, Datediff (Day, 0, DateAdd (Di, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))))) 1) = 0 THEN min (Dateadd (day, 0, datediff (day, 0, dateadd (day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (YE Ar, 0, @), 0))))))) 1 Else Null End As Monday, Case When (@@ Datefirst Datepart (Weekday, Min (Dateadd (DAY, 0, Dateadd (Day, 0, DateAdd , Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))) Dateadd (Di, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))) Datediff (Day, 0, Dateadd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))) 1))% 7 =

3 and datediff (Month, Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))) )), Min (Dateadd (DAY, 0, Datedd (DAY, DI, DATEADD (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0))))) 1) = 0 THEN MIN (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))) 1 When (@@ datefirst datepart (Weekday, Min (Dateadd (DAY, 0, Dateadd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, 0, @ ), 0))))))) 2)))% 7 = 3 and datediff (Month, min (Dateadd (DAY, 0, Dateadd (DAY, Di, Dateadd (Month, Mi, DateAdd Year, Datediff (Year, 0, @), 0)))))))))))), Min (Dateadd (DAY, 0, Datedd (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, Datediff) (Year, 0, @), 0)))))) 2) = 0 THEN MIN (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, DateAdd (Year) , Datediff (Year, 0, @), 0))))))) 2 else null end as tuesday, casser (@ @datefirst DatePart (Weekday, Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)) ))))))))))))))))))))))))))))% 7 = 4 THEN MIN (Dateadd (DAY, 0, Dateadd (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0 )))))))))))))))))))))))))))))))))))) (@@ Datefirst Datepart (Weekday, Min (Dateadd (DAY, 0, DATEADD (DAY, Di, Dateadd (Year, Datediff (Year, Datediff (Year, 0, @, 0)))))))) 1))% 7 = 4 and datediff (Month, min (Dateadd (DAY, 0, Dateadd (Di, Di, DateAdd (Month, Mi Dateadd (Year, Datediff (Year, 0, @

), 0)))))), Min (Dateadd (DAY, 0, Datedd (DAY, DI, DATEADD (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0 )))))))) 1) = 0 THEN min (Dateadd (DAY, 0, Dateadd (DAY, DI, Dateadd (Month, Mi, DateAdd (Year, Datediff (Year, 0, @) , 0)))))))))) 1 by (@@ datefirst datepart (Weekday, Min (Dateadd (DAY, 0, Dateadd (Day, Di, Dateadd (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0))))))))))) 2))% 7 = 4 and Datediff (Dateadd (DAY, 0, Dateadd (Di, Di, DateAdd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))), Min (Dateadd (DAY, 0, Datediff (Day, 0, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, 0))))))))))))) 2) = 0 THEN min (dateadd (day, 0, dateadd (day, di, dateadd Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))))))) 2 by (@@ Datefirst Datepart (Weekday, Min (Dateadd (DAY, 0, Datediff (day, 0, Dateadd (Di, Di, Dateadd (Month, Mi, DateAdd (Year, Datediff (Year, 0, @))))))) 3))% 7 = 4 and DA Tediff (Month, Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))) , Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))) 3))) = 0 THEN min (Dateadd (DAY, 0, Datedd (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))) 3 Else Null End As Wednesday, Case When (@@ Datefirst Datepart (Weekday, Min (Dateadd (DAY, 0, Datedd (DAY, DI, Dateadd (Month, Mi, Dateadd (Year, Datediff Year, 0, @), 0))))))))))))))))% 7 =

5 THEN min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))))) @@ datefirst datepart (Weekday, Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)) ))))) 1))% 7 = 5 and Datediff (Month, min (Dateadd (DAY, 0, Dateadd (Di, Di, Dateadd (Month, Mi, DateAdd (Year, Datediff , 0, @))))))), Min (Dateadd (DAY, 0, Datedd (DAY, DI, DATEADD (Month, Mi, DateAdd (Year, 0, 0, 0, 0, 0, @), 0))))))))) 1) = 0 THEN min (Dateadd (DAY, 0, Dateadd (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, Datediff (Year, 0, @))))))))))))) 1 by (@@ datefirst datepart (Weekday, Min (Dateadd (DAY, 0, DATEDD (DAY, DI, DATEADD (Month, Mi, DATEADD (Year, Datediff (Year, 0, @)))))))))) 2))% 7 = 5 and Datediff (Month, min (Dateadd (DAY, 0, Datedd (day, 0, dateadd , Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))), Min (Dateadd (DAY, 0, Dateadd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))) 2) = 0 THEN min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))) 2 WHEN (@@ datefirst datepart (Weekday, Min (Dateadd (DAY, 0, Dateadd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0) )))))) 3))% 7 = 5 and datediff (Month, min (Dateadd (DAY, 0, Dateadd (Di, Di, Dateadd (Month, Mi, DateAdd (Year, Datediff) Year, 0, @

), 0)))))), Min (Dateadd (DAY, 0, Datedd (DAY, DI, DATEADD (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0 ))))))) 3) = 0 THEN min (Dateadd (DAY, 0, Dateadd (DAY, DI, Dateadd (year, datediff (Year, 0, @) , 0)))))))))) 3 WHEN (@@ Datefirst Datepart (Weekday, Min (Dateadd (DAY, 0, Dateadd (Day, Di, Dateadd (Month, Mi, DateAdd (year, Datediff (Year, 0, @), 0)))))))) 4))% 7 = 5 and Datediff (Dateadd (DAY, 0, Dateadd (day, di, dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))), Min (Dateadd (DAY, 0, Datediff (Day, 0, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))))) 4) = 0 THEN min (Dateadd (day, 0, dateadd (day, di, dateadd (Di, Di, DateAdd) Month, Mi, Dateadd (Year, Datediff (Year, 0, @)))))) 4 else Null End As Thursday, Case When (@@ Datefirst Datepart (Weekday, Min (Dateadd (DAY, 0) , Datediff (Day, 0, Dateadd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))) )))))))))% 7 = 6 min min (Dateadd (DAY, 0, Dateadd (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0) ))))))))))))))))) (@@ Datefirst Datepart (Weekday, Min (Dateadd (DAY, 0, Dateadd (DAY, DI, Dateadd (Year, Datediff (Year, 0 , @), 0))))))))) 1)% 7 = 6 and datediff (Month, Min (Dateadd (DAY, 0, DATEDIFF (DAY, 0, DATEADD (Month, Mi, Dateadd (Year, 0)))))))))))))))))))))))))))))))). , Datediff (Year, 0, @), 0))))) 1) =

0 dam (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0))))) 1 WHEN (@@ Datefirst Datepart (Weekday, Min (Dateadd (DAY, 0, Dateadd (Day, Di, DateAdd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0 ))))))))))))))) 2)% 7 = 6 and datediff (Month, min (Dateadd (DAY, 0, DATEADD (Di, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff) (Year, 0, @), 0)))))))), Min (Dateadd (DAY, 0, Datedd (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, Datediff (Year, 0, @, 0)))))))))) 2) = 0 THEN min (Dateadd (DAY, 0, Datedd (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, Datediff) Year, 0, @), 0))))))))))) 2 WHEN (@@ Datefirst Datepart (Weekday, Min (Dateadd (DAY, 0, Dateadd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, 0))))))))))))) 3)% 7 = 6 and datediff (Month, Min (Dateadd (DAY, 0, Datediff (day, 0, dateadd) (Day, di, dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))))))), M In (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))) 3) = 0 THEN min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))) 3 WHEN (@@ Datefirst Datepart (Weekday, Min (Dateadd (DAY, 0, Dateadd (Day, Di, DateAdd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0 ))))))))))) 4)% 7 = 6 and datediff (Month, min (Dateadd (DAY, 0, Dateadd (Di, Di, Dateadd (year, mi, dateadd (year, datediff (Year, 0, @

), 0)))))), Min (Dateadd (DAY, 0, Datedd (DAY, DI, DATEADD (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0 ))))))))) 4) = 0 THEN min (Dateadd (DAY, 0, Dateadd (DAY, DI, Dateadd (Month, Mi, DateAdd (Year, Datediff (Year, 0, @) 0))))))))))) 4 WHEN (@@ Datefirst Datepart (Weekday, Min (Dateadd (DAY, 0, Dateadd (Day, Di, Dateadd (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0)))))))))) 5)% 7 = 6 and datediff (Month, min (Dateadd (DAY, 0, DATEDD (day, di, dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))), Min (Dateadd (DAY, 0, Datediff (Day, 0, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))))) 5) = 0 THEN min (Dateadd (DAY, 0, Datedd (DAY, DI, DATEADD) Month, Mi, Dateadd (Year, Datediff (Year, 0, @)))))) 5 else Null End As Friday, Case When (@@ Datefirst Datepart (Weekday, Min (Dateadd (Day, 0 , Datediff (Day, 0, Dateadd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))))))))))) ))))% 7 = 0 dam (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0))) )))))) (@@ Datefirst Datepart (Weekday, Min (Dateadd (DAY, 0, Dateadd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, 0, @ ), 0)))))))) 1)% 7 = 0 and datediff (Month, min (Dateadd (DAY, 0, Dateadd (Di, Di, Dateadd (Month, Mi, DateAdd Year, Datediff (Year, 0, @), 0)))))))))))), Min (Dateadd (DAY, 0, Datedd (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, Datediff) (Year, 0, @), 0)))))) 1) =

0 dam (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0))))) 1 WHEN (@@ Datefirst Datepart (Weekday, Min (Dateadd (DAY, 0, Dateadd (Day, Di, DateAdd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0 ))))))))))))) 2)% 7 = 0 and datediff (Month, min (Dateadd (DAY, 0, Dateadd (Di, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))))), Min (Dateadd (DAY, 0, Datedd (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, Datediff (Year, 0, @, 0)))))))))) 2) = 0 THEN min (Dateadd (DAY, 0, Datedd (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, Datediff) Year, 0, @), 0))))))))))) 2 WHEN (@@ Datefirst Datepart (Weekday, Min (Dateadd (DAY, 0, Dateadd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @)))))))))) 3)% 7 = 0 and datediff (Month, Min (Dateadd (DAY, 0, Datedd) (Day, di, dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))))))), M In (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))) 3) = 0 THEN min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))) 3 WHEN (@@ Datefirst Datepart (Weekday, Min (Dateadd (DAY, 0, Dateadd (Day, Di, DateAdd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0 ))))))))))))) 4)% 7 = 0 and Datediff (Month, min (Dateadd (DAY, 0, Dateadd (Di, Di, Dateadd (Month, Mi, DateAdd (Year, Datediff) (Year, 0, @

), 0)))))), Min (Dateadd (DAY, 0, Datedd (DAY, DI, DATEADD (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0 ))))))))) 4) = 0 THEN min (Dateadd (DAY, 0, Dateadd (DAY, DI, Dateadd (Month, Mi, DateAdd (Year, Datediff (Year, 0, @) 0))))))))))) 4 WHEN (@@ Datefirst Datepart (Weekday, Min (Dateadd (DAY, 0, Dateadd (Day, Di, Dateadd (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0)))))))))) 5)% 7 = 0 and Datediff (Month, Min (Dateadd (DAY, 0, Dateadd (DAY, Di, DateAdd) (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))), Min (Dateadd (DAY, 0, Datediff (Day, 0, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))))) 5) = 0 THEN min (Dateadd (DAY, 0, Datedd (DAY, DI, DATEADD) Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))) 5 WHEN (@@ DateFirst Datepart (Weekday, Min (Dateadd (DAY, 0, Datediff (day, 0, Dateadd (Di, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))))) 6)% 7 = 0 and DA Tediff (Month, Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))) , Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))) 6)) = 0 THEN min (Dateadd (DAY, 0, Datedd (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))

6 else null end as Saturdayfrom (select 0 as iunion all select 1 union all select 2 union all select 3union all select 4 union all select 5 union all select 6union all select 7 union all select 8 union all select 9union all select 10 union all select 11) M, (SELECT 0 As IUNION All SELECT 1 Union All Select 2 Union All Select 5 Union All Select 6 Union All Select 7 Union All Select 8 Union All Select 9 Union All Select 10UNION All SELECT 11 union all select 12 union all select 13 union all select 14union all select 15 union all select 16 union all select 17 union all select 18union all select 19 union all select 20 union all select 21 union all select 22union all select 23 union all select 24 union All Select 25 Union All Select 27 Union All Select 28 Union All Select 29 Union All Select 30) dwhere datediff (year, dateadd (year, datediff (year, 0, @), 0), Dateadd (day, di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))) = M .igroup by datediff (Month, 0, Dateadd (DAY, 0, DATEADD (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))) ))), Datediff (Week, Dateadd (Day, 0, Dated (DAY, 0, DATEADD (Year, Datediff (Year, 0, @), 0)), DateAdd (day, 0, datediff (day, 0, 0, Dateadd (Di, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))) 1 - ================ =======================================================================================================================================================

- Sunday calculated for the last day Select Month (Dateadd (Dare, 0, Datedd (DAY, Di, Dateadd (Year, Mi, Dateadd (Year, 0, @), 0)))))))))))))))))))) AS Month - Datepart (Week, Min (Dateadd (DAY, 0, Dateadd (Di, Di, Dateadd (Month, Mi, DateAdd (year, Datediff (Year, 0, @), 0)))))))))))))))))))))))))))))))) AS Weekofyear, Datediff (Week, Case When (@@ Datefirst Datepart (Weekday, Dateadd (Day, 0, Dateadd (Year , datediff (year, 0, @), 0)))))% 7 = 1 then dateadd (day, -1, dateadd (day, 0, datediff (day, 0, dateadd (year, datediff (year, 0, @), 0))) Else Dateadd (Day, 0, dateadd (day, 0, dateadd (Year, Datediff (Year, 0, @), 0)) end, case, (@@ DateFirst Datepart (Weekday , Dateadd (Day, 0, Datedd (Day, Di, Dateadd (Month, Mi, DateAdd (Month, Mi, Dateadd (Year, Datediff (Year, 0, 0))))))))% 7 = 1 Then Dateadd (Day, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))) )) Else Dateadd (Day, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))) End) 1 AS Weekofyear , Datediff (Week, Case When (@@ Datefirst Datepart (Weekday, Dateadd (Month, Datediff (Dateadd (DAY, 0, Dateadd (Month, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))))))))), 0))% 7 = 1 Then Dateadd (Month, Datediff (Month, 0, Min (Dateadd (day, 0 , Datediff (Day, Di, Dateadd (year, datediff (year, datediff (year, 0, @ @ @ @, @@

), 0)))))))))), 0) - 1 else dateadd (Month, Datediff (Dateadd (DAY, 0, Dateadd (Month, Di, DateAdd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @)))))))))))))), 0) End, Case When (@@ DateFirst Datepart (Weekday, Min (Dateadd (DAY, 0, Datediff , 0, Di, Dateadd (Month, Mi, DateAdd (Year, Datediff (Year, 0, @)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))) Datediff (Day, Di, Dateadd (DAY, MI, DATEADD (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))))))))))))) - 1 else min (dateAdd (day, 0, datediff (Day, 0, Dateadd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))))) End) 1 as weekofmonth -, dbo.udf_weekofyear MIN (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))))))))) DBO.UDF_WEEKOFMOFMONTH (Dateadd (Day, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))))) ) -, dbo.udf_weekofquarter (mi N (Dateadd (DAY, 0, Dateadd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))))))))), Case When (@@ datefirst datepart (Weekday, Min (Dateadd (DAY, 0, Dateadd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0) )))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))% 7 = 2 THEN min (Dateadd (DAY, 0, Dateadd (Di, Di, Dateadd (year, datediff (Year, 0, @) , 0)))))) Else Null End As Monday, Case When (@@ Datefirst Datepart (Weekday, Min (Dateadd (Day, 0, Dateadd (Day, Di, Dateadd (Month, Mi , Dateadd (Year, Datediff (Year, 0, @), 0))))))))))))))))))))))

3 Then min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))))). @@ datefirst datepart (Weekday, Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)) )))))) 1)% 7 = 3 and datediff (Month, min (Dateadd (DAY, 0, Dateadd (Di, Di, Dateadd (year, datediff (Year, Datediff , 0, @))))))), Min (Dateadd (DAY, 0, Datedd (DAY, DI, DATEADD (Month, Mi, DateAdd (Year, 0, 0, 0, 0, 0, @), 0))))))))) 1) = 0 THEN min (Dateadd (DAY, 0, Dateadd (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, Datediff (Year, 0, @, 0)))))))) 1 Else Null End As Tuesday, Case When (@@ Datefirst Datepart (Weekday, Min (Dateadd (Day, 0, Dateadd (Day, Di , Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))% 7 = 4 THEN min (Dateadd Day, Di, Dateadd (Month, Mi, Dateadd (Year, D) atediff (year, 0, @), 0)))))) when (@@ datefirst datepart (weekday, min (dateadd (day, 0, datediff (day, 0, dateadd (day, di, dateadd (month, Mi, Dateadd (Year, Datediff (Year, 0, @))))))))))) 1)% 7 = 4 and datediff (Month, min (dateadd (day, 0, datediff (day, 0, dateadd) (Day, di, dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))))), Min (Dateadd (DAY, 0, Datediff (day, 0, dateadd (day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))) 1) = 0 THEN min (Dateadd (day, 0, datediff (day, 0, dateadd Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))) 1 WHEN (@@ DateFirst

Datepart (Weekday, Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))) 2)% 7 = 4 and datediff (Month, min (Dateadd (DAY, 0, Dateadd (DAY, Di, Dateadd (year, datediff (Year, 0, @ ), 0)))))), Min (Dateadd (DAY, 0, Datedd (DAY, DI, DATEADD (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0 )))))))) 2) = 0 THEN min (Dateadd (DAY, 0, Dateadd (DAY, DI, Dateadd (Month, Mi, DateAdd (Year, Datediff (Year, 0, @) , 0))))) 2 Else Null End ASDAY, CASE WHEN (@@ Datefirst Datepart (Weekday, Min (Dateadd (Day, 0, Dateadd (Di, Di, Dateadd (Month) , Mi, Dateadd (Year, Datediff (Year, 0, @))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))) Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))) Dateadd (Di, Di, Dateadd (Month, Mi, DateAdd (Year, Datedi) FF (Year, 0, @), 0)))))))))))) 1)% 7 = 5 and datediff (Dateadd (DAY, 0, DATEADD (day, di, dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))), Min (Dateadd (DAY, 0, Datediff (Day, 0, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, 0))))))))) 1) = 0 THEN min (Dateadd (DAY, 0, DATEDD (DAY, DI, DATEADD Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))) 1 WHEN (@@ Datefirst Datepart (Weekday, Min (Dateadd (DAY, 0, Datediff (day, 0, Dateadd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))))) 2)% 7 =

5 And Datediff (Month, Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))) )), Min (Dateadd (DAY, 0, Datedd (DAY, DI, DATEADD (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0))))) 2) = 0 THEN min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))) 2 by (@@ Datefirst Datepart (Weekday, Min (Dateadd (DAY, 0, Dateadd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, 0, @ ), 0))))))))))) 3)% 7 = 5 and datediff (Month, min (Dateadd (DAY, 0, Dateadd (Di, Di, Dateadd (Month, Mi, DateAdd Year, Datediff (Year, 0, @), 0)))))))))))), Min (Dateadd (DAY, 0, Datedd (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, Datediff) (Year, 0, @), 0))))))) 3) = 0 THEN min (Dateadd (DAY, 0, DATEDD (Day, Di, Dateadd (Month, Mi, DateAdd (Year , Datediff (Year, 0, @), 0))))))) 3 else Null End As Thursday, CA Sehen (@@ datefirst datepart (Weekday, Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))). @), 0)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))). Datediff (Year, 0, @), 0))))))))))))))) 1)% 7 = 6 and datediff (Month, min (Dateadd (DAY, 0, Dateadd (Di, Di, DateAdd) (Month, Mi, Dateadd (Year, Datediff (Year, 0, @

), 0)))))), Min (Dateadd (DAY, 0, Datedd (DAY, DI, DATEADD (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0 )))))))) 1) = 0 THEN min (Dateadd (DAY, 0, Dateadd (DAY, DI, Dateadd (Month, Mi, DateAdd (Year, Datediff (Year, 0, @) , 0))))))))) 1 by (@@ Datefirst Datepart (Weekday, Min (Dateadd (DAY, 0, Dateadd (Day, Di, Dateadd (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0))))))))))) 2)% 7 = 6 and datediff (Month, Min (Dateadd (DAY, 0, Dateadd (Di, Di, DateAdd) (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))), Min (Dateadd (DAY, 0, Datediff (Day, 0, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, 0))))))))))))) 2) = 0 THEN min (dateadd (day, 0, dateadd (day, di, dateadd Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))) 2 by (@@ DateFirst Datepart (Weekday, Min (Dateadd (DAY, 0, Datediff (day, 0, Dateadd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))))))))) 3)% 7 = 6 and Datediff (Month, Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, 0))))) , Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))) 3))) = 0 THEN min (Dateadd (DAY, 0, Datedd (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))) 3 WHEN (@@ Datefirst Datepart (Weekday, Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0))))))))) 4)% 7 =

6 and datediff (Month, Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))) )), Min (Dateadd (DAY, 0, Datedd (DAY, DI, DATEADD (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0))))) 4) = 0 THEN MIN (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))))) 4 else Null End As Friday, Case When (@@ Datefirst Datepart (Weekday, Min (Dateadd (DAY, 0, Dateadd (Day, Di, Dateadd (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))) (Year, Datediff (Year, 0, @), 0))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))). (Month, Mi, Dateadd (Year, Datediff (Year, 0, @))))))))) 1)% 7 = 0 and Datediff (Month, Min (Dare, 0, Datediff (day, 0, DateAdd (Day, Di, Dateadd (Month, Mi, D Ateadd (Year, Datediff (Year, 0, @)))))))))))), Min (Dateadd (DAY, 0, Datedd (Di, Di, Dateadd (Month, Mi, DateAdd (Year) Datediff (Year, 0, @), 0))))))) 1) = 0 THEN min (Dateadd (DAY, 0, Dateadd (Day, Di, DateAdd (Month, Mi, DateAdd) (Year, Datediff (Year, 0, @), 0))))))))) 1 by (@@ Datefirst Datepart (Weekday, Min (Dateadd (Day, 0, Dateadd (Day, Di Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @))))))))) 2)% 7 = 0 and Datediff (Month, min (Dateadd (day, 0, datediff) Day, 0, Dateadd (Di, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @

), 0)))))), Min (Dateadd (DAY, 0, Datedd (DAY, DI, DATEADD (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0 )))))))) 2) = 0 THEN min (Dateadd (DAY, 0, Dateadd (DAY, DI, Dateadd (Month, Mi, DateAdd (Year, Datediff (Year, 0, @) 0)))))))))) 2 by (@@ Datefirst Datepart (Weekday, Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0)))))))))))))) 3)% 7 = 0 and Datediff (Month, Min (Dateadd (DAY, 0, Dateadd (Day, Di, DateAdd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))), Min (Dateadd (DAY, 0, Datediff (Day, 0, Dateadd (Month, MI, Dateadd (Year, Datediff (Year, 0, 0))))))))))))))) 3) = 0 THEN min (Dateadd (day, 0, dateadd (day, di, dateadd (Di, DateAdd) Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))))) 3 by (@@ DateFirst Datepart (Weekday, Min (Dateadd (DAY, 0, Datediff (day, 0, Dateadd (Di, Di, Dateadd (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0))))))))) 4)% 7 = 0 AND Datediff (Month, Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, 0))))) , Min (Dateadd (DAY, 0, Datediff (Day, 0, Dateadd (Day, Di, Dateadd (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0)))) 4) = 0 THEN min (Dateadd (DAY, 0, Datedd (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))) 4 WHEN (@@ datefirst datepart (Weekday, Min (Dateadd (DAY, 0, Dateadd (Day, Di, Dateadd (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0)))))))) 5)% 7 =

0 and datediff (Month, Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))) )), Min (Dateadd (DAY, 0, Datedd (DAY, DI, DATEADD (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0))))) 5) = 0 dam (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))))) 5 Else Null End As Saturday, Case When (@@ Datefirst Datepart (Weekday, Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))) (Year, Datediff (Year, 0, @), 0))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))). (Month, Mi, Dateadd (Year, Datediff (Year, 0, @))))))))))))) 1)% 7 = 1 and Datediff (Month, min (Dateadd (DAY, 0, Datediff (day, 0, Dateadd (Day, Di, Dateadd (Month, Mi Dateadd (Year, Datediff (Year, 0, @), 0))))))))), Min (Dateadd (DAY, 0, Datedd (DAY, Di, Dateadd (Month, Mi, DateAdd Year, Datediff (Year, 0, @), 0))))))))) 1) = 0 THEN min (Dateadd (DAY, 0, Dateadd (DAY, DI, DATEADD (Month, Mi, DATEADD (Year, Datediff (Year, 0, @), 0)))))) 1 by (@@ Datefirst Datepart (Weekday, Min (Dateadd (DAY, 0, Dateadd (DAY, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @)))))))))))))))) 2)% 7 = 1 and Datediff (Month, Min (DateAdd (DAY, 0, Datediff) (Day, 0, Dateadd (Di, Di, Dateadd (Month, Mi, DateAdd (Year, Datediff (Year, 0, @

), 0)))))), Min (Dateadd (DAY, 0, Datedd (DAY, DI, DATEADD (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0 )))))))) 2) = 0 THEN min (Dateadd (DAY, 0, Dateadd (DAY, DI, Dateadd (Month, Mi, DateAdd (Year, Datediff (Year, 0, @) 0)))))))))) 2 by (@@ Datefirst Datepart (Weekday, Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0))))))))))))))) 3)% 7 = 1 and datediff (Month, min (Dateadd (DAY, 0, Dateadd (Di, Di, DateAdd) (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))) MI, Dateadd (Year, Datediff (Year, 0, 0))))))))))))))) 3) = 0 THEN min (Dateadd (day, 0, dateadd (day, di, dateadd (Di, DateAdd) Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))))) 3 by (@@ DateFirst Datepart (Weekday, Min (Dateadd (DAY, 0, Datediff (day, 0, Datead (Di, Di, Dateadd (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0)))))))))) 4)% 7 = 1 and Datediff (Month, Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, 0))))) , Min (Dateadd (DAY, 0, Datediff (Day, 0, Dateadd (Day, Di, Dateadd (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0)))) 4) = 0 THEN min (Dateadd (DAY, 0, Datedd (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))) 4 WHEN (@@ datefirst datepart (Weekday, Min (Dateadd (DAY, 0, Dateadd (Day, Di, Dateadd (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0)))))))) 5)% 7 =

1 and datediff (Month, Min (Dateadd (DAY, 0, Datedd (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))) )), Min (Dateadd (DAY, 0, Datedd (DAY, DI, DATEADD (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0))))) 5) = 0 dam (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))))) 5 WHEN (@@ datefirst datepart (Weekday, Min (Dateadd (DAY, 0, Dateadd (Day, Di, Dateadd (Month, Mi, DateAdd (Year, 0, @ ), 0)))))))))))))) 6)% 7 = 1 AND datediff (Month, min (Dateadd (DAY, 0, Dateadd (Di, Di, DateAdd (Month, Mi, DateAdd Year, Datediff (Year, 0, @), 0)))))))))))), Min (Dateadd (DAY, 0, Datedd (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, Datediff) (Year, 0, @), 0)))))))) 6) = 0 THEN min (Dateadd (DAY, 0, Dateadd (Day, Di, Dateadd (Month, Mi, DateAdd (Year , Datediff (Year, 0, @), 0))))))

6 else null end as Sundayfrom (select 0 as iunion all select 1 union all select 2 union all select 3union all select 4 union all select 5 union all select 6union all select 7 union all select 8 union all select 9union all select 10 union all select 11) M, (SELECT 0 As IUNION All SELECT 1 Union All Select 2 Union All Select 5 Union All Select 6 Union All Select 7 Union All Select 8 Union All Select 9 Union All Select 10UNION All SELECT 11 union all select 12 union all select 13 union all select 14union all select 15 union all select 16 union all select 17 union all select 18union all select 19 union all select 20 union all select 21 union all select 22union all select 23 union all select 24 union All Select 25 Union All Select 27 Union All Select 28 Union All Select 29 Union All Select 30) dwhere datediff (year, dateadd (year, datediff (year, 0, @), 0), Dateadd (day, di, Datead (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))) ) = M.IGROUP by datediff (Month, 0, Dateadd (DAY, 0, Dateadd (Day, Di, Dateadd (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0 )))))))), Datediff (Week, Case When (@@ Datefirst Datepart (Weekday, Dateadd (Day, 0, Datedd (Year, Datediff (Year, 0, @), 0)) ))))% 7 = 1 Then Dateadd (DAY, 0, DATEDIFF (Day, 0, Dateadd (Year, Datediff (Year, 0, @), 0))) Else DateAdd (DAY, 0, Datediff (Day, 0, Dateadd (Year, Datediff (Year, 0, @), 0))) End, Case When (@@ DateFirst Datepart (Weekday, Dateadd (day, 0, datediff (day, 0, Dateadd (Di, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @

), 0)))))))% 7 = 1 then dateadd (day, -1, dateadd (day, 0, datediff (day, 0, dateadd (day, di, dateadd (month, mi, dateadd (year, Datediff (Year, 0, @), 0)))))))))))) Else Dateadd (DAY, 0, DATEADD (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, Datediff (Year, 0 , @), 0))))) 1 - ================================== = - The following is the old version can ignore - two new year calendars - increase the date of the date and years! - Sunday is in "last week"! (Note Weekofyear, weweekofmonth with Myweekofyear, Myweekofmonth Difference) - Note that DateName is different due to the SQL Server language version or date format! - This test environment is: SQL Server 2000 Simplified Chinese version Windows Simplified Chinese version Declare @ DateTimeSet @ = '1995-02-25 11:00:50 '- 1995-01-01 is exactly a Sunday Select @ as date, Dateadd (Year, Datediff (Year, 0, @), 0) AS's first day, DateAdd (Year, 1 Datediff (Year, 0, @), 0) -1 AS, the last day of the year, Dateadd (Quarter, Datediff (0, @), 0) AS, Dateadd (Quarter, 1 Datediff) (Quarter, 0, @), 0) -1 AS, last day of the season, dateadd (Month, Datediff (Month, 0, @), 0) AS, DateAdd (Month, 1 Datediff) Month, 0, @), 0) -1 AS last day , DateAdd (Week, Datediff (Week, 0, @), 0) AS A week, Dateadd (Week, 1 Datediff (Week, 0, @), 0) -1 AS Search SELECT Month (Dateadd (DAY, 0, dateadd (DAY, 0, DATEADD (DAY, DI, DATEADD (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0))))))) Month, Datepart (Week, MIN (Dateadd (DAY, 0, Dateadd (Day, Di, Dateadd (year, datediff (Year, 0, @

), 0))))))))))))))))) AS Weekofyear, Datediff (Week, Dateadd (Month, 0, Min (Dateadd (DAY, 0, DAY, DI, Di, 0, DateAdd , Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))))))))))))), 0), Min (Dateadd (DAY, 0, DATEDD (Day, Di, DateAdd (Di, DateAdd) Month, Mi, Dateadd (Year, Datediff (Year, 0, @))))))))) 1 AS Weekofmonth, Case When Datename (Weekday, Min (Dateadd (Day, 0, Datediff (day, 0, Dateadd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))))))))))))))))) = 'Sunday' Then Min (Dateadd (Day, 0, Datediff (Day, 0, Dateadd (Di, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @)))))) Else Null End AS Sunday, Case When Datename (Weekday, Min (Datead) Day, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))))) = 'Monday' Then Min (Dateadd (DAY, 0, Dateadd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))))))))) when Datename (Weekday, MIN (Dateadd (day, 0, date Diff (DAY, 0, DATEADD (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))) 1) = 'Monday' and Datediff (Month) , Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))), MIN Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))) 1) = 0 THEN Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))) 1 Else NULL Monday, Case When Datename (Weekday, Min (Dateadd (DAY, 0, Dateadd (DAY, Di, Dateadd (year, datediff (Year, 0, @

), 0))))))))))))))) = 'Tuesday' Then Min (Dateadd (DAY, 0, Dateadd (DAY, DI, Dateadd (year, datediff (Year, 0 , @), 0))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))). When Datename (Weekday, Min (Dateadd (DAY, 0, Dateadd (DAY, DI, Dateadd (Year, Datediff (Year, 0, @, 0)))))))))) 1) = 'Tuesday' and Datediff (Month, Min (Dateadd (DAY, 0, Dateadd (Di, Di, Dateadd (Month, Mi, Dateadd (Year, 0)))))))))))))))))))))))))))))))). Datediff (Year, 0, @), 0))))))) 1) = 0 THEN min (Dateadd (DAY, 0, Dateadd (Day, Di, DateAdd (Month, Mi, DateAdd) (Year, Datediff (Year, 0, @), 0)))))))))) 1 When Datename (Weekday, Min (Dateadd (Day, 0, Dateadd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @)))))))))))))) 2) = 'Tuesday' and Datediff (Month, Min (Dateadd (DAY, 0, DATEDIFF (Day, 0, DateAdd Day, di, dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))))))))))))), Min (DAY) , 0, Dateddd (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))) 2) = 0 THEN MIN (DateAdd) (Day, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))) 2 else Null End AS Tuesday Tuesday , Case When Datename (Weekday, Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))) ))))) = 'Wednesday' Then Min (Dateadd (DAY, 0, Dateadd (Day, Di, Dateadd (year, datediff (Year, 0, @

), 0)))))))))))))) When Datename (Weekday, Min (Dateadd (DAY, 0, Dateadd (DAY, DI, Dateadd (year, datediff (Year, 0, @), 0)))))))))) 1) = 'Wednesday' and Datediff (Month, Min (Dateadd (DAY, 0, Dateadd (Di, Di, Dateadd (Month, Mi, DateAdd Year, Datediff (Year, 0, @), 0)))))))))))), Min (Dateadd (DAY, 0, Datedd (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, Datediff) (Year, 0, @), 0)))))))) 1) = 0 THEN MIN (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, DateAdd (Year) Datediff (Year, 0, @), 0))))))))))))) 1 When Datename (Weekday, Min (Dateadd (DAY, 0, Dateadd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @)))))))))))))) 2) = 'Wednesday' and Datediff (Month, Min (Dateadd (Day, 0, Dateadd (day, 0, dateadd (DAY, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))), Min (DateAdd (DAY, 0, Dated (DAY, 0, DATEADD (Di, Di, DateAdd) (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))))) 2) = 0 THEN MIN (DateAdd (day, 0, Datediff (Day, Di, Dateadd (Month, Mi, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))) 2 When Datename (WeekDay, Min (Dateadd (DayAdd " , 0, Datedd (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))) 3) = 'Wednesday' and Datediff (Month, Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))) Min (Dateadd (DAY, 0, Datedd (DAY, DI, DATEADD (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0))))) 3) = 0 Then min (Dateadd (DAY, 0, Dateadd (Day, Di, Dateadd (year, datediff (year, 0, @ @ @ @@

), 0)))))) 3 else Null End AS Wednesday, Case When Datename (Weekday, Min (Dateadd (DAY, 0, Dateadd (Di, Di, Dateadd (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0))))))))))))))))))))))))))))))))))))))))))) = 'Thursday' Then Min (Dateadd (Day, 0, Dateadd (Day, Di, Dateadd (Month, Mi Dateadd (Year, Datediff (Year, 0, @))))))))))))))))))))))))))))))))))))))))))))))))))))))... Mi, Dateadd (Year, Datediff (Year, 0, 0))))))))))))) 1) = 'Thursday' and Datediff (Month, Min (Dateadd (DAY, 0, Dated) (DAY, 0, DateAdd) Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))))), Min (Dateadd (Day, 0, Datediff (Day, 0, DateAdd (Di, Di) , dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))) 1) = 0 THEN min (Dateadd (day, 0, dateadd (day, 0, dateadd (DAY , Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))) 1 When Datename (Weekday, Min (Dateadd (day, 0, datediff (day, 0, Datead (Di, Di, Dateadd (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0))))))))))) 2) = 'Thursday' and Datediff (Month, MIN (Dateadd (DAY, 0, Dateadd (Di, Di, Dateadd (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0)))))))), Min (Dateadd (DAY, 0, Datedd (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, Datediff (Year, 0, @, 0)))))))))) 2) = 0 THEN min (Dateadd (DAY, 0, Datedd (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, Datediff) Year, 0, @), 0))))))))) 2 When Datename (Weekday, Min (Dateadd (DAY, 0, Dateadd (Day, Di, Dateadd (Month, Mi, DateAdd (Year) Datediff (Year, 0, @), 0)))))) 3) = 'Thursday'

And Datediff (Month, Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))) ), Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))) 3 = 0 THEN min (Dateadd (DAY, 0, Dateadd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))))) 3 When Datename (Weekday, Min (Dateadd (DAY, 0, Dateadd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)) )))) 4) = 'Thursday' and datediff (Month, min (Dateadd (DAY, 0, Dateadd (DAY, Di, Dateadd (Year, Datediff (Year, 0 , @), 0)))))))), Min (Dateadd (DAY, 0, Dateadd (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, Datediff (Year, 0, @) 0)))))))))) 4) = 0 THEN min (Dateadd (DAY, 0, Dateadd (DAY, DI, DATEADD (Month, Mi, DateAdd (Year, 0, @), 0)))))))) 4 else Null End AS Thursday, Case When Datename (Weekday, Min (Dateadd (Day, 0, Date Diff (DAY, Di, Dateadd (DAY, MI, DATEADD (Month, Mi, DateAdd (Year, Datediff (Year, 0, 0))))))))))))))) = 'Friday' Then Min (DateAdd (DAY, 0, Datediff (Day, Di, Dateadd (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))) WHEN Datename (Weekday, Min (Datead (DAY) , 0, Datediff (Day, 0, Dateadd (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))) 1) = 'Friday' and Datediff (Month, Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))) MIN (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (year, datediff (year, 0, @ @ @ @ @

), 0)))))) 1) = 0 THEN min (Dateadd (DAY, 0, Dateadd (DAY, DI, Dateadd (year, datediff (Year, 0 , @), 0)))))))) 1 When Datename (Weekday, Min (Dateadd (DAY, 0, Dateadd (DAY, DI, Dateadd (Month, Mi, Dateadd (Year, Datediff) Year, 0, @), 0))))))) 2) = 'Friday' and Datediff (Month, Min (Dateadd (DAY, 0, Dateadd (Di, Di, DateAdd (Month, Mi, Dateadd (Year, Datediff (Year, 0, 0)))))))))))))), Min (DateAdd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, DateAdd) (Year, Datediff (Year, 0, @), 0))))))))))) 2) = 0 Then min (Dateadd (DAY, 0, Dateadd (Day, Di, Dateadd (Month, MI) Dateadd (Year, Datediff (Year, 0, @), 0)))))))))) 2 When Datename (Weekday, Min (Dateadd (Day, 0, Dateadd (Day, Di, DateAdd (Di, DateAdd) Month, Mi, Dateadd (Year, Datediff (Year, 0, @))))))))) 3) = 'Friday' and datediff (Month, Min (Dateadd (DAY, 0, Datediff (day, 0, Dateadd (Di, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))))))))))))))))), Min (DAY, 0, Datediff (Day, 0, Dateadd (Di, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))) 3) = 0 THEN min (DateAdd Day, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))) 3 When Datename (Weekday, Min (Dateadd (Day, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))) 4) = ' Friday 'And Datediff (Month, Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (year, datediff (Year, 0, @

), 0)))))), Min (Dateadd (DAY, 0, Datedd (DAY, DI, DATEADD (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0 ))))))))) 4) = 0 THEN min (Dateadd (DAY, 0, Dateadd (DAY, DI, Dateadd (Month, Mi, DateAdd (Year, Datediff (Year, 0, @) , 0)))))) 4 When Datename (Weekday, Min (Dateadd (DAY, 0, Dateadd (Day, Di, Dateadd (Year, Datediff (Year, 0 , @), 0))))) 5) = 'Friday' and Datediff (Month, Min (Dateadd (DAY, 0, Dateadd (Di, Di, Dateadd (Month, Mi, DateAdd) (Year, Datediff (Year, 0, @), 0))))))))), Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0))))))))) 5) = 0 THEN min (Dateadd (DAY, 0, Datedd (DAY, DI, DATEADD (Month, Mi, Dateadd Year, Datediff (Year, 0, @), 0)))))) 5 else Null End AS Friday, Case When Datename (Weekday, Min (Dateadd (DAY, 0, DATEDIFF (day, 0, dateadd (day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))))))))))))) = 'Saturday' Then MIN (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))))))) When Datename (WeekDay , Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))) 1)) = 'Saturday' and datediff (Month, MIN (Dateadd (DAY, 0, Dateadd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0) ))))), Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))) )) 1) =

0 dam (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0))))) 1 When Datename (Weekday, Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))) 2) = 'Saturday' and datediff (Month, Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @ ), 0)))))), Min (Dateadd (DAY, 0, Datedd (DAY, DI, DATEADD (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0 )))))))) 2) = 0 THEN min (Dateadd (DAY, 0, Dateadd (DAY, DI, Dateadd (Month, Mi, DateAdd (Year, Datediff (Year, 0, @) , 0)))))))) 2 When Datename (Weekday, Min (Dateadd (DAY, 0, Dateadd (Day, Di, Dateadd (Year, Datediff (Year, 0 , @), 0)))))))) 3) = 'Saturday' and datediff (Month, Min (Dateadd (DAY, 0, Dateadd (Di, Di, Dateadd (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0))))))))), Min (Dateadd (day, 0, datediff , 0, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))) 3) = 0 THEN min (DateAdd (day, 0, datediff (Day, 0, Dateadd (Di, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))) 3 When Datename (WeekDay, Min (Dateadd (DAY, 0, Datediff (Day, 0, Dateadd (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))) 4) = 'Saturday' and Datediff Month, Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))), Min (Dateadd (DAY, 0, Dateadd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))))

4) = 0 THEN MIN (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))))) 4 When Datename (Weekday, Min (Dateadd (DAY, 0, Dateadd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)) )))) 5) = 'Saturday' and datediff (Month, Min (Dare, 0, Datedd (DAY, Di, Dateadd (Month, Mi, DateAdd (Year, Datediff (Year, Datediff 0, @, 0))))))), Min (Dateadd (DAY, 0, Datedd (DAY, DI, DATEADD (Month, Mi, DateAdd (Year, Datediff (Year, 0, @ ), 0))))) 5) = 0 THEN min (Dateadd (DAY, 0, Dateadd (DAY, DI, DATEADD (Year, Datediff (Year, 0 , @), 0)))))))) 5 When Datename (Weekday, Min (Dateadd (DAY, 0, Dateadd (DAY, DI, Dateadd (Month, Mi, Dateadd (Year, Datediff) Year, 0, @), 0))))))))) 6) = 'Saturday' and Datediff (Month, Min (Dateadd (DAY, 0, Dateadd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))))))))), Min (DateAdd (DAY, 0, Datediff (D Ay, 0, Dateadd (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))) 6) = 0 THEN MIN (DateAdd (day, 0, Datediff (Day, 0, Dateadd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))))

6 else null end as Saturday from (select 0 as iunion allselect 1union allselect 2union allselect 3union allselect 4union allselect 5union allselect 6union allselect 7union allselect 8union allselect 9union allselect 10union allselect 11) M, (select 0 as iunion allselect 1union allselect 2union allselect 3union allselect 4union allselect 5union allselect 6union allselect 7union allselect 8union allselect 9union allselect 10union allselect 11union allselect 12union allselect 13union allselect 14union allselect 15union allselect 16union allselect 17union allselect 18union allselect 19union allselect 20union allselect 21union allselect 22union allselect 23union allselect 24union allselect 25union allselect 26union allselect 27union allselect 28union allselect 29union AllSelect 30) Dwhere Datediff (Month, Dateadd (Year, Datediff (Year, 0, @), 0), Dateadd (Di, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0 ))))) = Mi GROUP BY dateDiff (Month, 0, Dateadd (day, 0, dateadd (DAY, 0, DateAdd (D) Ay, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))), DatePart (Week, Dateadd (Day, 0, Datediff (Day, 0, DateAdd (day , Di, Dateadd (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0))))))))))))) - ================================================================================================================================================= ================================== Select Month (min (Dateadd (DAY, 0, DATEDIFF (Day, 0, DateAdd (DAY, DI, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @))))))))))))))))))))))))))))))))))))))) AS Month - DatePart (Week, Min (Dateadd (day, 0, datediff (day, 0, Dateadd (Di, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @

), 0)))))))))))))) AS Weekofyear, Datediff (Week, Case When Datename (Weekday, Dateadd (Day, 0, Dateadd (Year, Datediff (Year, 0, @), 0) ))) = 'Sunday' Then Dateadd (Day, 0, dateadd (day, 0, dateadd (year, datediff (year, 0, @), 0))) ELSE DateAdd (DAY, 0, Datediff (Day, Datediff (Year, Datediff (Year, 0, @), 0))) End, Case When Datename (Weekday, Dateadd (Day, 0, Dateadd (day, di , Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))))))))) = 'Sunday' Ten Dateadd (Day, -1, Dateadd (day, 0, datediff (day, 0 , Dateadd (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))) Else Dateadd (DAY, 0, DATEDIFF (day, 0, dateadd (day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))) End) 1 As Myweekofyear, Datediff (Week, Case When Datename (Weekday, Dateadd (Month, Datediff) Month, 0, Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))) ), 0)) = 'Sunday' Ten Dateadd (Month, Datediff (Month, 0, Min (Dateadd (DAY, 0, Dateadd (Di, Di, Dateadd (Month, Mi, DateAdd (Year, Datediff) Year, 0, @), 0))))))))))), 0) - 1 else dateadd (Month, Datediff (Month, 0, Min (Dateadd (DAY, 0, Dateadd (day, di , Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))))))))))), 0) End, Case When Datename (Weekday, Min (Dateadd (DAY, 0, Datediff (Day , 0, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @))))))))))))))))))))))))) = 'Sunday' Then Min (Dateadd (day, 0, datediff (Day, 0, Dateadd (Di, Di, Dateadd (Month, Mi, DateAdd (Year, Datediff (Year, 0, @

), 0)))))))) - 1 else min (dateadd (DAY, 0, Dateadd (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, Datediff (Year, 0, @) , 0)))))))) 1 AS Myweekofmonth, Case When Datename (Weekday, Min (Dateadd (DAY, 0, Dateadd (Di, Di, Dateadd (Month, Mi, DateAdd Datediff (Year, 0, @), 0))))))))))))))))))))) = 'Monday' Ten Min (Dateadd (DAY, 0, Dateadd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @)))))))))))))) Else Null End AS Monday, Case When Datename (Weekday, Min (Dateadd (Day, 0, Datedd (Day, 0, Dateadd (Day, 0, DateAdd , Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @)))))))))))))))))))))))) = 'Tuesday' Then Min (Dateadd (day, 0, datediff (day, 0, dateadd) (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))))))))))))))))))))))))))))))))))))))))))))))))) Dateadd (Di, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @))))))) 1) = 'Tuesday' and Datediff (Month, Min (Dateadd (DAY, 0, Datediff (Day , 0, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))), Min (Dateadd (DAY, 0, Datediff (day, 0, Dateadd (Di, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))) 1) = 0 THEN MIN (DateAdd (day, 0, datediff (day, 0, Dateadd (Di, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))) 1 else Null End AS Tuesday, Case When Datename (Weekday, Min) Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))))))) = 'Wednesday' Then MIN (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (year, datediff (year, 0, @ @ @ @ @

), 0)))))))))))))) When Datename (Weekday, Min (Dateadd (DAY, 0, Dateadd (DAY, DI, Dateadd (year, datediff (Year, 0, @), 0)))))))))) 1) = 'Wednesday' and Datediff (Month, Min (Dateadd (DAY, 0, Dateadd (Di, Di, Dateadd (Month, Mi, DateAdd Year, Datediff (Year, 0, @), 0)))))))))))), Min (Dateadd (DAY, 0, Datedd (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, Datediff) (Year, 0, @), 0)))))))) 1) = 0 THEN MIN (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, DateAdd (Year) Datediff (Year, 0, @), 0)))))) 1 When Datename (Weekday, Min (Dateadd (Day, 0, Dateadd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @)))))))))))))) 2) = 'Wednesday' and Datediff (Month, Min (Dateadd (Day, 0, Dateadd (day, 0, dateadd (DAY, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))), Min (DateAdd (DAY, 0, Dated (DAY, 0, DATEADD (Di, Di, DateAdd) (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))))) 2) = 0 THEN MIN (DateAdd (day, 0 , Datediff (Day, 0, Dateadd (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))) 2 Else Null End AS Wednesday, Case When Datename (Weekday, Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))))))))))))))))))))))))))) = 'Thursday' Ten Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))) When Datename (Weekday, Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))) )) 1) = 'Thursday'

And Datediff (Month, Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))) ), Min (Dateadd (DAY, 0, dateadd (DAY, DI, DATEADD (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0)))) 1 = 0 THEN min (Dateadd (DAY, 0, Dateadd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))))) 1 When Datename (Weekday, Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)) )))) 2) = 'Thursday' and datediff (Month, min (Dateadd (DAY, 0, Dateadd (Di, Di, Dateadd (year, datediff (Year, 0 , @), 0)))))))), Min (Dateadd (DAY, 0, Dateadd (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, Datediff (Year, 0, @) 0)))))))) 2) = 0 THEN min (Dateadd (DAY, 0, Datedd (DAY, DI, DATEADD (Month, Mi, DateAdd (Year, Datediff (Year, 0, 0, @), 0))))))) 2 When Datename (Weekday, Min (Dateadd (DAY, 0, DATED (DAY, DI, DATE) Add (Month, Mi, Dateadd (Year, Datediff (Year, 0, @)))))))))) 3) = 'Thursday' and Datediff (Month, Min (Dateadd (DAY, 0, Datediff (Day, 0, Dateadd (Di, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))), Min (Dateadd (DAY, 0, DATEDIFF (day, 0, dateadd) (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))))))) 3) = 0 THEN min (Dateadd (day, 0, datediff (day, 0 , Dateadd (Di, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))))

3 Else Null End AS Thursday, Case When Datename (Weekday, Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (year, datediff (Year, 0, @ @ @ ), 0))))))))))))) = 'Friday' Then Min (Dateadd (DAY, 0, Dateadd (DAY, DI, Dateadd (Month, Mi, DateAdd (Year, Datediff (Year, 0 , @), 0))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))). When Datename (Weekday, Min (Dateadd (DAY, 0, Dateadd (DAY, DI, Dateadd (Year, Datediff (Year, 0, @, 0)))))))) 1) = 'Friday' and Datediff (Month, min (Dateadd (DAY, 0, Dateadd (Di, Di, Dateadd (Month, Mi, Dateadd (Year, 0)))))))))))))))))))))))))))))))). Datediff (Year, 0, @), 0))))))) 1) = 0 THEN min (Dateadd (DAY, 0, Dateadd (Day, Di, DateAdd (Month, Mi, DateAdd) (Year, Datediff (Year, 0, @), 0)))))))) 1 When Datename (Weekday, Min (Dateadd (Day, 0, Dateadd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))) 2) = 'Friday' and datediff (Month, MIN (Dateadd (Day, 0, Datedd (Day, Di, Dateadd (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0))))))))), Min (Dateadd (DAY, 0, Dateadd (DAY, DI, DATEADD (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0)) ))))) 2) = 0 THEN min (Dateadd (DAY, 0, Dateadd (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0 ))))))) 2 When Datename (Weekday, Min (Dateadd (DAY, 0, Dateadd (DAY, DI, Dateadd (Month, Mi, DateAdd (Year, 0, @ ), 0))))) 3) = 'Friday'

And Datediff (Month, Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))) ), Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))) 3 = 0 THEN min (Dateadd (DAY, 0, Dateadd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))))) 3 When Datename (Weekday, Min (Dateadd (DAY, 0, Dateadd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)) )))) 4) = 'Friday' and datediff (Month, MIN (Dateadd (DAY, 0, Dateadd (DAY, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0 , @), 0)))))))), Min (Dateadd (DAY, 0, Dateadd (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, Datediff (Year, 0, @) 0)))))))))) 4) = 0 THEN min (Dateadd (DAY, 0, Dateadd (DAY, DI, DATEADD (Month, Mi, DateAdd (Year, 0, @), 0))))) 4 else Null End AS Friday, Case When Datename (Weekday, Min (Dateadd (DAY, 0, DA TEDIFF (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))))))) = 'Saturday' dam (Dateadd (DAY, 0, Datediff (Day, Di, Dateadd (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))) WHEN Datename (Weekday, Min (Datead (DAY) , 0, Datediff (Day, 0, Dateadd (DAY, DI, DATEADD (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0))))) 1) = 'Saturday' and Datediff (Month, MIN (Dateadd (DAY, 0, Dateadd (Day, Di, Dateadd (year, datediff (Year, 0, @ @@

), 0)))))), Min (Dateadd (DAY, 0, Datedd (DAY, DI, DATEADD (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0 )))))))) 1) = 0 THEN min (Dateadd (DAY, 0, Dateadd (DAY, DI, Dateadd (Month, Mi, DateAdd (Year, Datediff (Year, 0, @) , 0))))) 1 When Datename (Weekday, Min (Dateadd (DAY, 0, Dateadd (DAY, DI, Dateadd (Month, Mi, DateAdd (Year, 0 , @), 0))))) 2) = 'Saturday' and Datediff (Month, Min (Dateadd (DAY, 0, Dateadd (Di, Di, Dateadd (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0))))))))), Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0)))))))))) 2) = 0 THEN min (Dateadd (DAY, 0, Datedd (DAY, DI, DATEADD (Month, Mi, DateAdd Year, Datediff (Year, 0, @), 0)))))))) 2 When Datename (Weekday, Min (Dateadd (Day, 0, Dateadd (Day, Di, Dateadd (Month, Mi , Dateadd (Year, Datediff (Year, Year, @))))))))) 3) = 'Saturday' and Datediff (Month, Min (Dateadd (Daya) , 0, Datedd (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))), Min (DateAdd (DAY, 0, Datediff (Day, Di, Dateadd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))) 3) = 0 THEN MIN (DateAdd (DAY, 0, Datediff (Day, 0, Dateadd (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))) 3 When Datename (WeekDay, Min (DateAdd) (Day, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))) 4) = 'Saturday'

And Datediff (Month, Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))) ), Min (Dateadd (DAY, 0, dateadd (Day, Di, Dateadd (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0)))) 4)) = 0 THEN min (Dateadd (DAY, 0, Dateadd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))))) 4 When Datename (Weekday, Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)) ))) 5) = 'Saturday' and datediff (Month, Min (Dateadd (DAY, 0, Dateadd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0 , @), 0)))))))), Min (Dateadd (DAY, 0, Dateadd (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, Datediff (Year, 0, @) 0)))))) 5) = 0 THEN min (Dateadd (DAY, 0, Dateadd (DAY, DI, DATEADD (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0))))))) 5 else Null End AS Saturday, Case When Datename (Weekday, Min (Dateadd (Day, 0, DA TEDIFF (Day, Di, Dateadd (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, Datediff (Year, 0, @)))))))))))))))))))))))))))) = 'Sunday' Then Min (Dateadd (Day, 0, Datediff (Day, Di, Dateadd (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))) WHEN Datename (Weekday, Min (Datead (DAY) , 0, Dateddd (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))) 1) = 'Sunday' and Datediff (Month, MIN (Dateadd (DAY, 0, Dateadd (Day, Di, Dateadd (year, datediff (Year, 0, @ @@

), 0)))))), Min (Dateadd (DAY, 0, Datedd (DAY, DI, DATEADD (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0 )))))))) 1) = 0 THEN min (Dateadd (DAY, 0, Dateadd (DAY, DI, Dateadd (Month, Mi, DateAdd (Year, Datediff (Year, 0, @) , 0))))) 1 When Datename (Weekday, Min (Dateadd (DAY, 0, Dateadd (DAY, DI, Dateadd (Month, Mi, DateAdd (Year, 0 , @), 0)))))) 2) = 'Sunday' and datediff (Month, min (Dateadd (DAY, 0, Dateadd (Di, Di, Dateadd (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0))))))))), Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0)))))))))) 2) = 0 THEN min (Dateadd (DAY, 0, Datedd (DAY, DI, DATEADD (Month, Mi, DateAdd Year, Datediff (Year, 0, @), 0)))))))) 2 When Datename (Weekday, Min (Dateadd (Day, 0, Dateadd (Day, Di, Dateadd (Month, Mi , Dateadd (Year, Datediff (Year, 0, @))))))))))) 3) = 'Sunday' and datediff (Month, Min (Dateadd (DAY , 0, Datedd (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))), Min (DateAdd (DAY, 0, Datediff (Day, Di, Dateadd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))) 3) = 0 THEN MIN (DateAdd (DAY, 0, Datediff (Day, 0, Dateadd (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))) 3 When Datename (WeekDay, Min (DateAdd) (Day, 0, Dateadd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))) 4) = 'Sunday'

And Datediff (Month, Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))) ), Min (Dateadd (DAY, 0, dateadd (Day, Di, Dateadd (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0)))) 4)) = 0 THEN min (Dateadd (DAY, 0, Dateadd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))))) 4 When Datename (Weekday, Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)) )))) 5) = 'Sunday' and datediff (Month, Min (Dateadd (DAY, 0, Dateadd (Di, Di, Dateadd (year, mi, dateadd (Year, Datediff (Year, 0 , @), 0)))))))), Min (Dateadd (DAY, 0, Dateadd (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, Datediff (Year, 0, @) 0)))))) 5) = 0 THEN min (Dateadd (DAY, 0, Dateadd (DAY, DI, DATEADD (Month, Mi, DateAdd (Year, Datediff (Year, 0, @), 0))))))) 6 When Datename (Weekday, Min (Dateadd (Day, 0, Dateadd (Day, Di, Date) Add (Month, Mi, Dateadd (Year, Datediff (Year, 0, @)))))))) 6) = 'Sunday' and Datediff (Month, Min (Dateadd (DAY, 0, Datediff (Day, 0, Dateadd (Di, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))), Min (Dateadd (DAY, 0, DATEDIFF (day, 0, dateadd) Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))))) 5) = 0 THEN min (Dateadd (day, 0, datediff (day, 0 , Dateadd (Di, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))))

6 else null end as Sunday from (select 0 as iunion allselect 1union allselect 2union allselect 3union allselect 4union allselect 5union allselect 6union allselect 7union allselect 8union allselect 9union allselect 10union allselect 11) M, (select 0 as iunion allselect 1union allselect 2union allselect 3union allselect 4union allselect 5union allselect 6union allselect 7union allselect 8union allselect 9union allselect 10union allselect 11union allselect 12union allselect 13union allselect 14union allselect 15union allselect 16union allselect 17union allselect 18union allselect 19union allselect 20union allselect 21union allselect 22union allselect 23union allselect 24union allselect 25union allselect 26union allselect 27union allselect 28union allselect 29union AllSelect 30) Dwhere Datediff (Month, Dateadd (Year, Datediff (Year, 0, @), 0), Dateadd (Di, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0 )))))) = Mi Group by datediff (Month, 0, Dateadd (day, 0, datediff (day, 0, Dateadd (Di, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))), Datediff (Week, Case When Datename (Weekday, Dateadd (day, 0, datediff (Day, 0, dateadd (Year, Datediff (Year, 0, @)))) = 'Sunday' Then Dateadd (Day, 0, Datedd (day, 0, dateadd (year, 0, dateadd , Datediff (Year, 0, @), 0))) Else Dateadd (Day, 0, Dated (DAY, 0, DATEADD (Year, Datediff (Year, 0, @), 0)) End, Case When Datename (Weekday, Dateadd (Day, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))) = 'Sunday 'Ten Dateadd (Day, 0, Datedd (Day, Di, Dateadd (year, datediff (year, datediff (year, datediff (year, datediff (year, 0, @ 0, @ @@