/ * T-SQL: 15 custom functions related to date time (UDF), Sunday as the last day of the week, unreasonable is collected or refined from the old articles from the old articles on Sunday. Tip: (@@ datefirst datepart (weekday, @ Date))% 7 Judging the week is the most insurance! Nothing to @@ DateFirst, unrelated to the language version @@ DateFirst may cause datepart (weekday, @ Date) different Whether @@ datefirst is equal to a few, no matter what language version of SQL Server is always constant! (@@ DateFirst DatePart (weekday, @ Date)): 2, 3, 4, 5, 6, 0, represent Monday to Sunday - * / create function udf_getage (@StartDate DateTime) Returns INTEGER - Return Exact Age Select DBo.udf_getage ('1949-10-01', getdate ()) beginreturn Datediff (Year, @ StartDate, @ EndDate) - case when datediff (day, dateadd (year, datediff (year, @ StartDate, @ EndDate), @ StartDate), @ EndDate)> = 0 then 0 else 1 endendgocreate function udf_DaysOfYearByDate (@Date datetime) RETURNS Integer - Returns the number of days of the year to judge Ping (365), Run (366) BeginReturn Datediff (Day, Datedd (Year, Datediff (Year, 0, @ Date), 0), Dateadd (Year, Datediff (Year, 0 @ Date) 1,0)) Endgocreate Function UDF_DAYSOFYEAR (@Year Integer) Returns INTEGER - The number of days returned to the year can be judged (365), Yu Run (366) year Beginre Turn Datediff (year, dateadd (year, @ Year - Year (0), 0), Dateadd (Year, @ Year - Year (0) 1,0)) endgocreate function udf_halfday (@date datetime) Returns DateTime - return @Date is the zero point of @date in the morning, @DATE is the 12th point of the afternoon. AsbeginReturn Case When DatePart (Hour, @ Date) <12 Then Dateadd (day, datediff (day, 0, @ Date), 0) - Morning to zero Else Dateadd (Hour, 12, dateadd (day, datediff (day, @ Date), 0)) - Between the afternoon to 12 Endendgocreate function udf_weekdiff (@startdate datetime, @ Enddate DateTime) Returns integer - return [@startdate, @
Enddate] Sunday is the last day of Week BeginReturn Datediff (Week, @ StartDate, @ Enddate) - 1 Case When (@@ DateFirst DatePart (WeekDay, @ startdate))% 7 = 1 THEN 1 else 0 end - case when (@@ DateFirst Datepart (weekday, @ Enddate))% 7 = 1 THEN 1 ELSE 0 EndendgocReate Function UDF_WEEKOFMONTH (@date datetime) - Back @date is the first few weeks of the month It is a week's last day Returns IntegerasbeginReturn Datediff (Week, Case When (@@ Datefirst Datepart (Weekday, Dateadd (Month, Datediff (Month, 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) 1ENDGOCREATE FUNCTION UDF_WEEKOFQUARTER (@Date datetime) - Returns to @date is the last day of the quarter is the last day of Week Returns IntasborginReturn Datediff (Week, Case When (WeekDay, Dateadd (Quarter, Datediff (Quarter, 0, @ Date), 0)))% 7 = 1 Then Dateadd (Quarter, Datediff (Quarter, 0, @ Date), 0) - 1 else DateAdd (Quarter, 0, @ Date), 0) End, Case When (@@ DateFirst DatePart (WeekDay, @ Date))% 7 = 1 Then @
Date - 1 else @date end) 1ENDGOCREATE FUNCTION UDF_WEEKOFYEAR (@date datetime) - Return @date is the last day of the year is the last day of the week Returns IntasbeginReturn 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, Dated (DAY, 0, Dateadd (Year, Datediff (Year, 0, @ Date), 0))) Else Dateadd (DAY, 0, DATEDIFF (DAY, 0, DATEADD (Year, Datediff (Year, 0, @ Date), 0)) - Date's first day: January 1 end, Case When (@@ DateFirst DatePart (WeekDay, @ Date))% 7 = 1 Then DateAdd (Day , -1, @DATE) 1ENDGOCREATE FUNCTION UDF_WEEKDAY (@ int, @ DateTime) Returns DateTime - Back to @Date Mapping to the Sunday of other days of the week is the last day of the week begin / * - - On Sunday, the last day of the week When @ <= 1 represents @Date to the week's week when @ = 2 represents @Date maps to the week's Tuesday when @ = 3 represents @date maps to the week's Wednesday is @ = 4 representative @Date maps to the week's Thursday when @ = 5 represents @date maps to the week's Friday when @ = 6 represents @Date maps to the week's Saturday when @> = 7 represents @Date map @Date to your week Sunday can be used in Zhouhui GROUP BY, supports New Year's Eve Data * / Return Dateadd (DAY, CASE WHEN (@@ DateFirst DatePart (WeekDay, @ Date))% 7 = 0 - Saturday Ten Case When @ Between 1 and 6 Then @ @ @
- 6 else1 end when (@@ DateFirst DatePart (weekday, @ Date))% 7 = 1 - Sunday (7) Ten Case when @ Between 1 and 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) endgocreate function udf_WeekdayDiff (@Weekday integer, @ StartDate datetime, @ EndDate datetime) returns integer-- - Back [@StartD ATE, @enddate] Monday to Sunday's number of Sundays is the last day of Week Begin - @WeekDay: 1: Monday, ..., 7: Sundayreturn Datediff (Week, @ startdate, @ Enddate) Case when (@@ DateFirst DatePart (Weekday, @ startdate))% 7 Case When (@@ DateFirst Datepart (Weekday, @ startdate))% 7 = 0 THEN 7 Else 0 end> @WeekDay% 7 1 THEN 0 else 1 End - Case When (@@ DateFirst Datepart (Weekday, @
Enddate))))% 7 Case When (@@ DateFirst DatePart (weekday, @ Enddate))% 7 = 0 THEN 7 else 0 end> = @WeekDay% 7 1 Then 0 else 1 end / * test: Declare @B DateTimedeclare @E datetimeset @B = '2004-01-29'set @e =' 2004-09-05'select @B as begindate, @ e as enddate, dbo.udf_weekdaydiff (1, @ b, @ e) as countryofmonday , dbo.udf_WeekdayDiff (2, @ b, @ e) as CountOfTuesday, dbo.udf_WeekdayDiff (3, @ b, @ e) as CountOfWednesday, dbo.udf_WeekdayDiff (4, @ b, @ e) as CountOfThursday, dbo.udf_WeekdayDiff ( 5, @ b, @ e) as CountOfFriday, dbo.udf_WeekdayDiff (6, @ b, @ e) as CountOfSaturday, dbo.udf_WeekdayDiff (7, @ b, @ e) as CountOfSunday * / endgocreate function udf_WeekdayID (@Date datetime) Returns Integer - Back @date is Monday Returns 1, ..., Sunday Returns 1begin - 1: Monday, ..., 7: Sundayreturn (@@ DateFirst DatePart (WeekDay, @ Date))% 7 Case WHEN (@@ DateFirst DatePart (WE Ekday, @DATE))% 7 <2 THEN 6 else -1 Endendgocreate Function UDF_NEXTGOCREATE FUNCTION UDF_NEXTWORKDATE (@date datetime) Returns DateTime - Return to @date's next business day begin / * declare @i intset @i = 3Declare @date datetimeset @ Date = '2005-01-02' - * / return case when (@@ DateFirst DatePart (weekday, @ Date))% 7 = 6 - Friday Then Dateadd (day, 3, @ Date) WHEN (@@ DateFirst DatePart (weekday, @ Date))% 7 =
0 - SATURDAY THEN DATEADD (DAY, 2, @ Date) Else Dateadd (day, 1, @ Date) Endendgocreate Function UDF_PREVIOSWORKDATE (@date datetime) Returns DateTime - Back to @Date's previous working day Begin / * Declare @i Intset @i = 3Declare @date datetimeset @date = '2005-01-02' - * / return case when (@@ DateFirst DatePart (weekday, @ Date))% 7 = 2 - Monday Then Dateadd (day, -3, @ Date), (@@ datefirst datepart (weekday, @ Date))% 7 = 1 - Sunday Ten Dateadd (day, -2, @ Date) Else DateAdd (day, -1, @ Date) EndendGOCREATE Function UDF_WORKDATEADD (@i integer, @ Date DateTime) Returns DateTime - Return to @Date plus a new value of a working day begindeclare @ tent @ = 0WHILE @
2 - Monday Ten Dateadd (@@ DateFirst DatePart (WeekDay, @ Date))% 7 = 1 - Sunday Ten Dateadd (DAY, -2, @ Date) Else DateAdd ( Day, -1, @ Date) end end @ = @ 1 endreturn @dateendgocreate function udf_getstar (@ datetime) returns varchar (100) - Return date belonging beginreturn (- Declare @ DateTime - set @ = getDate ) SELECT MAX (STAR) from (SELECT 'Capricorn' AS Star, 1 As [Month], 1 AS [Day] Union All Select 'Aquarius', 1, 20UNION All Select 'Pisces', 2,19Union All Select 'Peony Block', 3,21 ELON All Select 'Golden Block', 4, 20UNION All Select 'Double Subshot', 5,21 ELON All Select 'Cancer' ', 8, 23Union All Select' Scorpio ', 10, 24Union All Select' Sagittragard ', 11, 22Union All Select' Capricorn ', 12,22) Starswhere Dateadd (Month, [Month] - 1, DateAdd (Year, Year (@) - Year (0), 0)) [day] - 1 = (Select Max (Dateadd (Month, [Month] - 1, DateAdd (Year, Year (@) - Year (0), 0)) [Day] - 1) from (SELECT ' 'AS Star, 1 AS [Month], 1 as [Day] Union All Select' Aquarius ', 1, 20UNION All Select' Pisces ', 2,19 ELE SELECT' Peony Rock ', 3,21 ELE SELECT' Taurus ', 4, 20Union All Select' Double Subtock ', 5,21une All Select' Cancer ', 6,22UNION All Select' Leo, 7,23union All Select 'Virgo', 8,23union All Select 'Libra', 9 , 23Union All Select 'Scorpio', 10, 24Union All Select 'Skills'
