/ * T-SQL: 17 custom functions (UDF) related to date time (UDF), Sunday as the last day of the week, unreasonable is not affected by @@ DateFirst, language version is collected or refined from the old articles! 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))% 7: 2, 3, 4, 5, 6, 0, 1 Represents Monday to Sunday - * / create function udf_getage (@startdate datetime) returns integer - return to exact age SELECT DBO.UDF_GETAGE ('1949-10-01', getdate ()) BeginReturn Datediff (Year) , @ StartDate, @ Enddate - Case When Dated (Year, Datedd (Year, Datediff (Year, @ startdate, @ Enddate), @ StartDate), @ Enddate> = 0 THEN 0 else 1 Endend
Go
Create Function UDF_DAYSOFYEARBYDATE (@date datetime) Returns Integer - Return the number of days of the year can be judged (365), Run (366) BeginReturn Datediff (Day, Dateadd (Year, Datediff (Year, 0, @ Date), 0), Dateadd (Year, Datediff (Year, 0, @ Date) 1,0)) End
Go
Create function udf_daysofyear (@Year Integer) Returns INTEGER - Return the number of days of the year can be judged (365), Yun (366) BeginReturn Datediff (Day, Dateadd (Year, @ Year - Year (0), 0), Dateadd ( Year, @ Year - Year (0) 1,0)) End
Go
Create Function UDF_HALFDAY (@date datetime) Returns DateTime - Return @date is zero point in the morning, @DATE is the 12th point of the afternoon to return @Date BeginReturn Case When Datepart (Hour, @ Date) <12 Ten Dateadd (Day) Datediff (day, 0, @ Date), 0) - Morning to zero else dateadd (hour, 12, dateadd (day, datediff (day, 0, @ Date), 0)) - Nate in the afternoon to twelve Point Endnd
Go
Create function udf_weekdiff (@StartDate DateTime) Returns Integer - Return [@StartDate, @enDDate] Weekly 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 Endendgo
create function udf_WeekOfMonth (@Date datetime) - return @Date is the week where Sunday is the month of the last day of the week returns integerbeginreturn datediff (week, case when (@@ Datefirst datepart (weekday, dateadd (month, datediff (MONTH, 0, @ Date), 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
Go
create function udf_WeekOfQuarter (@Date datetime) - return @Date first few weeks of the quarter where Sunday is the last day of the week returns intbeginreturn 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, Case When (@@ DateFirst DatePart (WeekDay, @ Date))% 7 = 1 Then @date - 1 else @date end) 1ENDGO
Create Function UDF_WEEKOFYEAR (@date datetime) - Back @date is the last day of the year is the last day of Week Returns IntbeginReturn Datediff (Week, Case When (@@ Datefirst Datepart (Weekday, Dateadd (Day, 0 , Datediff (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 (Day, 0, Dateadd (Year, Datediff (Year, 0, @ Date), 0)) ) - Date's first day: January 1 end, casser (@@ DateFirst DatePart (weekday, @ Date))% 7 = 1 Then Dateadd (day, -1, @ Date) Else @ Date end) 1
Go
Create Function UDF_WEEKDAY (@ Int, @ Date DateTime) Returns DateTime - Back to @date Other days from Monday to Sunday, the other day of the week, the last day of the week is the last day of the week, begin / * - week Daily budget (last) week's last day When @ <= 1 represents @Date maps to the week's Monday when @ = 2 represents @Date maps to the week's Tuesday when @ = 3 represents @Date map Wednesday to the week when @ = 4 represents @Date maps to the week's Thursday. When @ = 5 represents @Date maps to the week's Friday when @ = 6 represents @Date map @Date to the Saturday of Saturday, @> = 7 Representatives Map @date maps to the Sunday of the week can be used to support New Year's data * / return dateadd (DAY, CASE WHEN)% in Wenghui GROUP BY. 7 = 0 - Saturns Then Case by @ Between 1 and 6 Then @ - 6 else 1 end when (@@ DateFirst DatePart (weekday, @ Date))% 7 = 1 - Sunday (7) Then 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 by @ Between 1 and 6 Then @ 1 - (@@ DateFirst Datepart (WeekDay, @ Date))% 7 Else 8 - (@@ DateFirst DatePart (WeekDay, @ Date))% 7 end end, @ Date) Endgo
Create Function UDF_WEEKDAYDIFF (@Weekday DateTime, @ Enddate DateTime) Returns Integer - Return [@startdate, @enddate] Monday to Sunday's number of Sundays is a week's last day 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 DateTime
Set @B = '2004-01-29'set @e =' 2004-09-05 '
SELECT @B as begindate, @ e as enddate, dbo.udf_weekdaydiff (1, @ b, @ e) as countofmonday, dbo.udf_weekdaydiff (2, @ b, @ e) as countrysteriesday, 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 * / end
Go
CREATE FUNCTION UDF_WEEKDAYID (@date datetime) returns integer - Return @date is Monday Returns 1, ..., Sunday Returns 1begin - 1: Monday, ..., 7: Sundayreturn (@@ DateFirst DatePart (Weekday, @Date))% 7 case when (@@ DateFirst DatePart (Weekday, @ Date))% 7 <2 THEN 6 ELSE -1 EndendGo
Create Function UDF_DAYOFQUARTER (@date datetime) - Returns @date is the first few days of the quarter Returns Integerasbegin / * Declare @date datetimeset @date = '2004-4-1' - * / return datediff (day, dateadd (quarter , Datediff (Quarter, 0, @ Date), 0), @ Date) 1END
Go
create function udf_DaysOfQuarterByDate (@Date datetime) - Returns the number of days where @date quarter returns integerbegin / * declare @date datetimeset @date = '2004-4-1' - * / return datediff (day, dateadd (Quarter, datediff ( Quarter, 0, @ Date), 0), DateAdd (Quarter, Datediff (Quarter, 0, @ Date) 1,0)) End
Go
Create Function UDF_NEXTWORKDATE (@date datetime) Returns DateTime - Return to @Date 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 (@@ DateFirst Date)% 7 = 0 - - Saturday Then Dateadd (Day, 2, @ Date) Else DateAdd (day, 1, @ Date) Endend
Go
Create Function UDF_PREVIOUSWORKDATE (@date datetime) Returns DateTime - Back to @Date's last business day begin / * declare @i intset @i = 3Declare @date datetimeset @date = '2005-01-02' - * / return case WHEN (@@ DateFirst DatePart (Weekday, @ Date))% 7 = 2 - Monday Ten Dateadd (@@ DateFirst DatePart (WeekDay, @ Date))% 7 = 1 - Sunday Ten Dateadd (day, -2, @ Date) Else Dateadd (day, -1, @ Date) Endendgo
Create Function UDF_WORKDATEADD (@i integer, @ Date DateTime) Returns DateTime - Return to @date plus a new value of a business day begindeclare @ tent @ = 0WHILE @
CREATE FUNCTION UDF_GETSTAR (@ DateTime) Returns Varchar (100) - Returns the scheme belonging, if there is a static constellation control code table directly in the query, JOIN efficiency is relatively higher, 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 SELECT 'Peony Block', 3,21 ELON All Select 'Golden Block', 4, 20UNION All Select 'Double Subshot', 5,21 ELON All Select 'Cancer', 6,22UNION All Select 'Leo, 7,23union All Select' Virgo ', 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 '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 UNION All Select 'Golden Block', 4, 20UNION All Select 'Double Subshot', 5,21 ELON All Select 'Cancer' 'Leo', 7,23uneion All Select 'Virgin', 8,23union All SELEC T 'Libra', 9, 23Union All Select 'Scorpio', 10, 24Union All Select 'Sagittarius', 11, 22Union All Select 'Capricorn', 12, 22) Starswhere @> = dateadd (Month, [Month] - 1, dateadd (Year, Year (@) - Year (0), 0)) [Day] - 1)) Endgouse Northwindselect A.BIRTHDATE, B.STARFROM Employees Alex Join (SELECT A. *, ISNULL (B. Month, 12) AS M, ISNULL (B.day, 31) AS DFROM Stars Aleft Join Stars Bon A.MONTH * 40 A.DAY Day) from Stars WHERE MONTH * 40 Day> A.MONTH * 40 A.DAY)) BON MONTH (A.BIRTHDATE) * 40 Day (A.BIRTHDATE)> = B.MONTH * 40 B.DAYAND MONTH (A.BIRTHDATE) * 40 day (a.birdhdate) SELECT @ CASE WHEN (@@ DateFirst DatePart (Weekday, @))% 7 = 0 - Saturday Then -5 When (@@ DateFirst DatePart (WeekDay, @))% 7 = 1 - Sunday ( 7) THEN-6 WHEN (@@ DateFirst DatePart (Weekday, @))% 7 Between 2 and 6 - Monday to Friday THEN 2 - (@@ DateFirst DatePart (WeekDay, @))% 7nd N. IFROM (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) N
