createfunctionudf_GetAge(@StartDatedatetime,@EndDatedatetime)
returnsinteger
--前往准确岁数selectdbo.udf_GetAge(1949-10-01,getdate())
begin
returndatediff(year,@StartDate,@EndDate)
-casewhendatediff(day,dateadd(year,datediff(year,@StartDate,@EndDate),@StartDate),@EndDate)>=0
then0
else
1
end
end
go
createfunctionudf_DaysOfYearByDate(@Datedatetime)
returnsinteger
--前往年的天数可判别平(365)、润(366)年
begin
returndatediff(day,dateadd(year,datediff(year,0,@Date),0),dateadd(year,datediff(year,0,@Date)+1,0))
end
go
createfunctionudf_DaysOfYear(@Yearinteger)
returnsinteger
--前往年的天数可判别平(365)、润(366)年
begin
returndatediff(day,dateadd(year,@year-year(0),0),dateadd(year,@year-year(0)+1,0))
end
go
createfunctionudf_HalfDay(@Datedatetime)
returnsdatetime
--前往@Date是上午前往@Date的零点,@Date是下战书前往@Date的十二点
begin
returncasewhendatepart(hour,@Date)<12
thendateadd(day,datediff(day,0,@Date),0)--上午回到零点
else
dateadd(hour,12,dateadd(day,datediff(day,0,@Date),0))--下战书回到十二点
end
end
go
createfunctionudf_WeekDiff(@StartDatedatetime,@EndDatedatetime)
returnsinteger
--前往[@StartDate,@EndDate]之间周数周日是当周的最初一天
begin
returndatediff(week,@StartDate,@EndDate)--+1
+casewhen(@@Datefirst+datepart(weekday,@StartDate))%7=1
then1
else
0
end
-casewhen(@@Datefirst+datepart(weekday,@EndDate))%7=1
then1
else0
end
end