function | function/*
T-sql:17 a date-time related Custom function (UDF), Sunday as the last day of the week, not affected by the @ @DateFirst, language version
are collected or refined from the old articles!
Tips:
(@ @Datefirst + datepart (weekday, @Date))% 7 judgment Week is the most insured! Independent of @ @DateFirst and language version
@ @DateFirst may cause datepart (weekday, @Date) different!
No matter what the @ @DateFirst equals, no matter what language version of SQL Server is always set under forever!
(@ @Datefirst + datepart (weekday, @Date))%7:2, 3, 4, 5, 6, 0, 1 respectively represent Monday to Sunday
-- */
Create function Udf_getage (@StartDate datetime, @EndDate datetime)
Returns integer
--Return to the exact age select Dbo.udf_getage (' 1949-10-01 ', GETDATE ())
Begin
Return DateDiff (year, @StartDate, @EndDate)
-Case when DateDiff (Day,dateadd (Year,datediff, @StartDate, @EndDate), @StartDate), @EndDate) >= 0
Then 0
Else
1
End
End
Go
Create function Udf_daysofyearbydate (@Date datetime)
Returns integer
--The number of days to return the year can be judged flat (365), run (366) years
Begin
Return 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
--The number of days to return the year can be judged flat (365), run (366) years
Begin
Return DateDiff (Day,dateadd, @year-year (0), 0), DateAdd (year, @year-year (0) + 1,0))
End
Go
Create function Udf_halfday (@Date datetime)
returns datetime
--Return @Date is the morning return of the @Date 0 points, @Date is back in the afternoon @Date 12 points
Begin
Return case when DATEPART (hour, @Date) < 12
Then DateAdd (Day,datediff (day,0, @Date), 0)--morning to 0.
Else
DATEADD (Hour,12,dateadd (Day,datediff (day,0, @Date), 0)--afternoon to 12.
End
End
Go
Create function Udf_weekdiff (@StartDate datetime, @EndDate datetime)
Returns integer
--Return to [@StartDate, @EndDate] The week of Sunday is the last day of the week
Begin
Return 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
End
End
Go
Create function Udf_weekofmonth (@Date datetime)
--Return @Date is the week of the month Sunday is the last day of the week
Returns integer
Begin
Return 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
&n
