- Increase the date of the date and years! - Sunday is in "last week"! (Note Weekofyear, Weekofmonth Differences) - Note Datename Value Because SQL Server language version or date The format is different! - 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 of the year, DateAdd (Year, 1 Datediff (Year, 0, @), 0) -1 AS On the last day, Dateadd (Quarter, Datediff (Quarter, 0, @), 0) The first day of the season, DateAdd (Quarter, 1 Datediff (Quarter, 0, @), 0) -1 AS On the last day, Dateadd (Month, Datediff (Month, 0, @), 0) AS AS, DateAdd (Month, 1 Datediff (Month, 0, @), 0) -1 AS's last day One day, Dateadd (Week, Week, 0, @), 0) The first day of the week, DateAdd (Week, 1 Datediff (Week, 0, @), 0) -1 AS last day
Select Dateadd (Day, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))) AS [Date], DateName (Weekday, Dateadd (Day, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))) AS [WeekDayname ], Datepart (Weekday, Dateadd (Day, 0, Dateadd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))))) As [weekday], (@@ datefirst datepart (Weekday, Dateadd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @) , 0)))))))))))))))))))))))))))))))))% 7 as [MyWeekDay]
, Datepart (Week, Dateadd (Day, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)))) AS Weekofyear, Datediff (Week, Case When (@@ Datefirst Datepart (Weekday, Dateadd (Day, 0, Dated (DAY, 0, DATEADD (Year, Datediff (Year, 0, @), 0)))% 7 = 1 Then Dateadd (Day, 0, Datedd (Day, 0, Dateadd (Year, Datediff (Year, 0, @), 0))) Else DateAdd (DAY, 0, Datediff (Day, Date , 0, Dateadd (Year, Datediff (Year, 0, @))) - Date The first day of the year: January 1 end, Case When (@@ DateFirst DatePart (Weekday, DateAdd ( Day, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @))))))))))))))))))))))))))% 7 = 1 THEN DATEADD ( Day, -1, 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)))) ND) 1 As Myweekofyear, Datediff (Week, Dateadd (Dateadd (DAY, 0, Dateadd (Day, Di, Dateadd (DiTdd (year, di, dateadd). , 0, @))))))), DateAdd (DAY, 0, Dateadd (Di, Di, Dateadd (year, datediff (Year, 0, @) , 0))))), DateAdd (DAY, 0, DATEDD (DAY, DI, DATEADD (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0)) )) 1 AS Weekofmonth
, Datediff (Week, Case When (@@ Datefirst Datepart (Weekday, Dateadd (Month, Dateddd (DAY, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))))))) 0, Dateadd (Di, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))))))))), 0) Else DateAdd (Month, Datediff (Month, 0, 0, Dateadd (Day, 0, Datedd (Day, Di, Dateadd (Month, Mi, Dateadd (Year, Datediff (Year, 0, @), 0))))), 0) End, Case WHEN (@@ Datefirst Datepart (Weekday, Dateadd (Day, 0, Datedd (Day, Di, 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)) )))) - 1 Else Dateadd (DAY, 0, Dateadd (DAY, DI, DATEADD (Month, Mi, Dateadd (Yea, Datediff (YEA R, 0, @), 0))))))))))) End 1 As Myweekofmonth, Datepart (DAYOFYear, Dateadd (DAY, 0, Dateadd (Day, Di, Dateadd (Month, Mi, DateAdd Year, Datediff (Year, 0, @), 0)))))))))))
From (SELECT 0 As IUNION All SELECT 1 Union All Select 2 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 3 union all select 4 union all select 5union 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 26UNION All SELECT 27 Union All Select 28 Union All Select 29 Union All Select 30) Dwhere Dated (Month, Dateadd (Year, Datediff (Year, 0, @), 0), Dateadd (DAY, DI, DATEADD (Month, Mi , dateadd (Year, Datediff (Year, 0, @), 0))))) = m.iorder by [date]
