T-SQL generates two new real calendars

xiaoxiao2021-03-06 54

Transfer from: http: //dev.9cbs.net/develop/Article/26/2644.shtm - two new year calendars - increase the date of the date and years! - Sunday is in "last week" ! (Note WeekOfyear, Weekofmonth and Myweekofyear, Myweekofmonth) - Note that Datename is different from 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 The first day, Dateadd (Year, 1 Datediff (Year, 0, @), 0) -1 AS at the last day of the year, DateAdd (Quarter, Datediff (0, @), 0) Since the season On the first day, Dateadd (Quarter, 0, @), 0) -1 AS last day, Dateadd (Month, Datediff (Month, 0, @), 0) AS One day, Dateadd (Month, 1 Datediff (Month, 0, @), 0) -1 AS's last day, Dateadd (Week, Datediff (Week, 0, @), 0) AS's first Day, Dateadd (Week, 1 Datediff (Week, 0, @), 0) -1 As the last day of the week Select Month (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, Datedd (DAY, 0, DateAdd) Day, di, dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @))))))))))))))))))))))))))))))))))))))))))))))))) AS Weekofyear, Datediff (Week, Dateadd (Month, Datediff (Dateadd (DAY, 0, Datediff (day, 0, Dateadd (Di, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))))), 0), Min (Dateadd (DAY, 0, Datediff (Day , 0, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @))))))) 1 AS Weekofmonth, Case When Datename (Weekday, Min (Dateadd (Day, 0, Datedd (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))))))) = 'Sunday'

THEN min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))) ELSE NULL END Sunday, Case When Datename (Weekday, Min (Dateadd (DAY, 0, Dateadd (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, Dateadd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @ ), 0)))))))) 1) = 'Monday' 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 ELSE N Ull End AS Monday, Case When Datename (Weekday, Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, DateAdd (Year, Datediff (Year, 0, @) , 0)))))))))) = 'Tuesday' Then Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, DateAdd (Year, 0, 0, @), 0))))))))))))))))))))))))))))). When Datename (Weekday, Min (Dare, 0, Datedd (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, 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 (year, datediff (Year, 0 , @), 0)))))) 2) = 'Tuesday' 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, Case When Datename (Weekday, 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))))))))) When Datename (WeekDay , Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))) 1)) = 'Wednesday' 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) = 'Wednesday' and datediff (Month, Min (Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, DateAdd (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) = 'Wednesday' 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, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))) 3 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 'Then Min (Dateadd (DAY, 0, Datedd (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) = '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)))))))) 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) = 'Thursday' and Datediff (Dateadd (DAY, 0, Datedd (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, Datediff (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 (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 (Di, Di, DateAdd (Month, Mi, Dateadd (Year, 0)))))))))))))))))))))))))))))))). Datediff (Year, 0, @), 0)))))))))) 4) = 0 Then min (Dateadd (DAY, 0, Dateadd (Day, Di, DateAdd (Month, Mi, DateAdd) (Year, Datediff (Year, 0, @), 0)))))) 4 else Null End AS Thursday, Case When Datename (Weekday, Min (Dateadd (DAY, 0, Dateadd (day, 0, dateadd , Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @)))))))))))))))))))))))) = 'Friday' 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, @), 0 )))))))))) 1) = '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 (year, datediff (Year, 0) , @), 0)))))) 1) = 0 THEN min (Dateadd (DAY, 0, Dateadd (DAY, DI, DATEADD (year, datediff (year, datediff , 0, @, 0))))))))) 1 When Datename (Weekday, Min (Dateadd (DAY, 0, Datedd (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))))) = 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, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))) ))) 3) = 'Friday' 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))))))) 3) = 0 THEN min (Dateadd (DAY, 0, Dateadd (DAY, DI, Dateadd (Month, Mi, Dateadd (Year, 0, 0, @), 0))))))) 3 When Datename (Weekday, Min (Dateadd (DAY, 0, DATEDD (DAY, DI, DATEAD) D (Month, Mi, Dateadd (Year, Datediff (Year, 0, @)))))))) 4) = 'Friday' 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, @)))))))) 4) = 0 THEN min (DateAdd (day, 0, datediff (day, 0 , Dateadd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))) 4 When Datename (Weekday, Min (DateAdd (DAY, 0, Datediff) Day, 0, Dateadd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))) 5) = '

Friday '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)))))))))) 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, Dateadd (Diateddd (year, di, dateadd (year, datediff (year, datediff 0, @, 0))))))))))))))) = 'Saturday' Ten Min (Dateadd (DAY, 0, Datedd (DAY, DI, Dateadd (Month, Mi, Dateadd (Year, Datediff) Year, 0, @), 0))))))))))))))))))))))))))))))))))))))))))))))))))))). (Year, 0, @), 0)))))))) 1) = '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)))) 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, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))) )) 2) = '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 )))))))) 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, 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 (Di, Di, Dateadd (Month, MI , Dateadd (Year, Datediff (Year, 0, @)))))))))) 4) = 'Saturday' and Datediff (Month, Min (Dare, 0 , Datediff (Day, Di, Dateadd (Month, Mi, 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, @)))))))) 4) = 0 THEN min (DateAdd (day, 0, Datediff (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))) 4 When Datename (WeekDay, Min (Datead (DAY) , 0, Datedd (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))) 5) = 'Saturday'

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, 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, @ @@

转载请注明原文地址:https://www.9cbs.com/read-114793.html

9cbs

New Post(0)