/ * There is only one sentence for each function! It is actually from my other blog.
T-SQL generates two new real Quality calendars http://blog.9cbs.net/playyueer/archive/2004/04/07/2860.aspx
T-SQL generates a simple Queen Year of the Calendar T-SQL with the date of the date and years http://blog.9cbs.net/playyuer/archive/2004/04/2859.aspx
Due to use (@@ DateFirst DatePart (WeekDay, @ Date))% 7 Judging the week is not related to DateFirst, and can adapt to various language versions of SQL Server
* /
- On Sunday, the last day of the week
Create Function UDF_WEEKOFYEAR (@date datetime) - We are in the last day of the last year - Sunday count (previous) Week - When Zhouhui GROUP BY, do not have a cross-year data, or both Group by year - group by year (date), month (date), dbo.udf_WeekOfYear (date), dbo.udf_WeekOfMonth (date) returns intasbeginreturn (select datediff (week, case when (@@ datefirst datepart (Weekday, Dateadd (Day, 0, Dated (Day, 0, Dateadd (Year, Datediff (Year, 0, @ Date), 0)))))% 7 = 1 Then DateAdd (day, -1, dateadd (day , 0, Datediff (Day, 0, Dateadd (Year, Datediff (Year, 0, @ Date), 0))) Else Dateadd (Day, 0, Dated) (year, 0, dateadd (year, datediff (Year, 0 , @ Date), 0)) - Date, the first day of the year: January 1 end, Case When (@@ DateFirst DatePart (WeekDay, @ Date))% 7 = 1 Then DateAdd (day, -1, @ Date) Else @date end) 1) end
Go
create function udf_WeekOfQuarter (@date datetime) returns intasbeginreturn (select datediff (week, case when (@@ datefirst datepart (weekday, dateadd (Quarter, datediff (Quarter, 0, @ date), 0)))% 7 = 1 then Dateadd (Quarter, Datediff (Quarter, 0, @ Date), 0) - 1 else dateadd (quarter, datediff (quarter, 0, @ Date), 0) end, casage (@@ DateFirst Datepart (weekday, @ Date )))% 7 = 1 Then @date - 1 else @date end) 1) endGO
Create Function UDF_WEEKOFMONTH (@date datetime) - Ask @Date is the last day of the month - Sunday calculation (last) week - used for GROUP by Zhouhui, do not have a moon New Year data, or GROUP BY Year, Month - Group by Year (Date), Month (Date), dbo.udf_weekofyear (date), dbo.udf_weekofmonth (Date) Returns IntasbeginReturn (Select Datediff (Week, Case When (@ @datefirst DatePart (Weekday, Dateadd (Month, Datediff (Month, 0, 0))% 7 = 1 Then Dateadd (Month, Datediff (Month, 0, @ Date), 0) - 1 else dateadd (Month, Datediff (Month, 0, @ Date), 0) End, Case When (@@ DateFirst DatePart (WeekDay, @ Date))% 7 = 1 THEN @ DATE-1 ELSE @Date End) 1) End
Go
Create Function UDF_WEEKDAY (@ Int, @ Date DateTime) Returns DateTimeasbegin / * - Sunday Calculated (Previous) Weekly When @ <= 1 represents @Date maps to the week's Monday is @ = 2 representative Map @date to the week's Tuesday when @ = 3 represents @Date maps to the week's Wednesday when @ = 4 represents @Date maps to the week's Thursday when @ = 5 represents @Date map @Date map to the week Friday is when @ = 6 represents @Date maps to the Saturday of the week when @> = 7 represents @Date maps to the Sunday of the week can be used to support the New Year's Eve data * / return in Zhouhui GROUP BY. - @ Date, Datename (Weekday, @ Date), (@@ Datefirst DatePart (Weekday, @ Date))% 7,3 - (@@ DateFirst Datepart (WeekDay, @ Date))% 7, DateAdd (Day , Case, (@@ DateFirst DatePart (Weekday, @ Date))% 7 = 0 - Saturns Then Case When @ Between 1 and 6 Then @ - 6 else 1 End When (@@ DateFirst Datepart (Weekday, @ Date))% 7 = 1 - Sunday (7) THEN CASE WHEN @ Between 1 a ND 6 THEN @ - 7 else 0 End
WHEN (@@ DateFirst DatePart (Weekday, @ Date))% 7 Between 2 and 6 - Monday to Friday Then Case When @ between 1 and 6 Then @ 1 - (@@ DateFirst DatePart (weekday, @ Date ))% 7 else 8 - (@@ DateFirst DatePart (WeekDay, @ Date))% 7 end end, @ Date)) / * Test: Select Date, Datename (WeekDay, Date), 'Map to:', DBO .udf_weekday (2, date), Datename (Weekday, DBo.udf_weekday (1, date) from Torder by Date
- =============== SET datefirst 4Declare @ Int, @ a int set @ = 1
Select Date, Datename (WeekDay, Date), (@@ DateFirst DatePart (Weekday, Date))% 7,3 - (@@ DateFirst Datepart (Weekday, Date))% 7, DateAdd (DAY, Case When) @Datefirst DatePart (Weekday, Date))% 7 = 0 - Saturns Then Case When @ Between 2 and 7 Then - (7- @) else @ End when (@@ DateFirst DatePart (WeekDay, Date))% 7 = 1 - Sunday Then Case When @ Between 2 and 7 Then - (7 - @) - 1 else @ - 1 End
When (@@ DateFirst DatePart (Weekday, Date))% 7 Between 2 and 6 - Monday to Friday Then Case When @ Between 2 and 7 Then @ - (@@ DateFirst DatePart (Weekday, Date))% 7 Else 8 - (@@ DateFirst DatePart (Weekday, Date))% 7 Endend, Date) from Dorder by Dorder By Date * / End
create function udf_WeekDiff (@BeginDate datetime, @ EndDate datetime) returns integerbeginreturn (select datediff (week, @ BeginDate, @ EndDate) - 1 case when (@@ datefirst datepart (weekday, @ BeginDate))% 7 = 1 THEN 1 ELSE 0 end - case when (@@ DateFirst DatePart (weekday, @ Enddate))% 7 = 1 THEN 1 ELSE 0 END) EndGo - Test: Declare @B DateTime, @ e DateTimeSet @B = '2004- 12-12'Set @e = '2004-12-13'
Select dbo.udf_weekdiff (@ b, @ e) 1 AS cross Week, dbo.udf_weekdiff (@ b, @ e) AS interval number, datediff (week, @ b, @ e) as [datediff (week, startdate , Enddate)], @ b AS [Date], DBO.UDF_WEEKDAY (1, @ b) AS date is located on Monday
