15 个与日期时间相关的自定义函数

/*T-SQL: 15 个与日期时间相关的自定义函数(UDF),周日作为周的最后一天,均不受 @@DateFirst、语言版本影响都是从老文章里收集或提炼出来的!提示:(@@Datefirst + datepart(weekday,@Date)) % 7 判断周几是最保险的! 与 @@DateFirst 无关,与语言版本无关@@DateFirst 可能会导致 datepart(weekday,@Date) 不一样!无论 @@DateFirst 等于几,无论是什么语言版本的 SQL Server 下面永远恒成立!(@@Datefirst + datepart(weekday,@Date)): 2、3、4、5、6、0、1 分别代表 周一 到 周日-- */

create function udf_GetAge(@StartDate datetime,@EndDate datetime)returns integer-- 返回精确年龄 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         endend

go

create function udf_DaysOfYearByDate(@Date datetime)RETURNS integer-- 返回年的天数 可判断 平(365)、润(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-- 返回年的天数 可判断 平(365)、润(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-- 返回 @Date 是 上午 返回 @Date 的零点,@Date 是 下午 返回 @Date 的十二点asbeginreturn case when datepart(hour,@Date) < 12                 then dateadd(day,datediff(day,0,@Date),0) --上午归到 零点            else                 dateadd(hour,12,dateadd(day,datediff(day,0,@Date),0)) --下午归到 十二点       endend

go

create function udf_WeekDiff(@StartDate datetime,@EndDate datetime)returns integer-- 返回 [@StartDate , @EndDate] 之间周数 周日是当周的最后一天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         endend

go

create function udf_WeekOfMonth(@Date datetime)-- 返回 @Date 是所在月的第几周 周日是当周的最后一天returns integerasbeginreturn 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               ) + 1end

go

create function udf_WeekOfQuarter(@Date datetime)-- 返回 @Date 是所在季度的第几周 周日是当周的最后一天returns intasbeginreturn 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               ) + 1end

go

create function udf_WeekOfYear(@Date datetime)-- 返回 @Date 是所在年的第几周 周日是当周的最后一天returns intasbeginreturn 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,datediff(day,0,dateadd(year,datediff(year,0,@Date),0))) --date 所在年的第一天 即: 一月一号                 end                ,case when (@@Datefirst + datepart(weekday,@Date)) % 7 = 1                           then dateadd(day,-1,@Date)                      else                           @Date                 end               ) + 1end

go

create function udf_WeekDay(@ int,@Date datetime)returns datetime-- 返回 @Date 映射到 所在周的其他天 周日是当周的最后一天begin/*--周日算作(上一)周的最后一天  当 @ <= 1 代表将 @Date 映射到 所在周的星期一 当 @ = 2 代表将 @Date 映射到 所在周的星期二 当 @ = 3 代表将 @Date 映射到 所在周的星期三 当 @ = 4 代表将 @Date 映射到 所在周的星期四 当 @ = 5 代表将 @Date 映射到 所在周的星期五 当 @ = 6 代表将 @Date 映射到 所在周的星期六 当 @ >= 7 代表将 @Date 映射到 所在周的星期日 可用于按周汇总 Group by,均支持跨年跨月数据*/return dateadd(day               ,case when (@@Datefirst + datepart(weekday,@Date)) % 7 = 0 --周六                          then case when @ between 1 and 6                                         then @ - 6                                    else                                         1                               end                     when (@@Datefirst + datepart(weekday,@Date)) % 7 = 1 --周日(七)                          then case when @ between 1 and 6                                         then @ - 7                                    else                                         0                               end                     when (@@Datefirst + datepart(weekday,@Date)) % 7 between 2 and 6 --周一至周五                          then case when @ between 1 and 6                                         then @ + 1 - (@@Datefirst + datepart(weekday,@Date)) % 7                                    else                                         8 - (@@Datefirst + datepart(weekday,@Date)) % 7                               end                end               ,@Date)end

go

create function udf_WeekdayDiff(@Weekday integer,@StartDate datetime,@EndDate datetime)returns integer-- -- 返回 [@StartDate , @EndDate] 之间周一 到 周日的个数 周日是当周的最后一天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 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*/end

go

create function udf_WeekdayID(@Date datetime)returns integer-- 返回 @Date 是 Monday 返回 1, ... ,是 Sunday 返回 1begin--1: Monday , ... ,7: Sundayreturn (@@Datefirst + datepart(weekday,@Date)) % 7       + case when (@@Datefirst + datepart(weekday,@Date)) % 7 < 2                   then 6              else                   -1         endend

go

create function udf_NextWorkDate(@Date datetime)returns datetime-- 返回 @Date 的下一个工作日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)       endend

go

create function udf_PreviousWorkDate(@Date datetime)returns datetime-- 返回 @Date 的上一个工作日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)             when (@@Datefirst + datepart(weekday,@Date)) % 7 = 1 -- Sunday                 then dateadd(day,-2,@Date)             else                 dateadd(day,-1,@Date)        endend

go

create function udf_WorkDateAdd(@i integer,@Date datetime)returns datetime-- 返回 @Date 加上一段 @i 个工作日的新值begindeclare @ intset @ = 0while @ < abs(@i)begin   set @Date = case when @i >= 0                         then --dbo.udf_nextworkdate(@Date)                              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)                              end                    else                         --dbo.udf_previousworkdate(@Date)                         case when (@@Datefirst + datepart(weekday,@Date)) % 7 = 2 -- Monday                                   then dateadd(day,-3,@Date)                              when (@@Datefirst + datepart(weekday,@Date)) % 7 = 1 -- Sunday                                   then dateadd(day,-2,@Date)                              else                                   dateadd(day,-1,@Date)                         end               end               set @ = @ + 1 endreturn @Dateend

go

create function udf_GetStar (@ datetime)RETURNS varchar(100)-- 返回日期所属星座BEGINRETURN(--declare @ datetime--set @ = getdate()select max(star)from(select @#魔羯座@# as star,1 as [month],1 as [day]union all select @#水瓶座@#,1,20union all select @#双鱼座@#,2,19union all select @#牡羊座@#,3,21union all select @#金牛座@#,4,20union all select @#双子座@#,5,21union all select @#巨蟹座@#,6,22union all select @#狮子座@#,7,23union all select @#处女座@#,8,23union all select @#天秤座@#,9,23union all select @#天蝎座@#,10,24union all select @#射手座@#,11,22union all select @#魔羯座@#,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 @#水瓶座@#,1,20union all select @#双鱼座@#,2,19union all select @#牡羊座@#,3,21union all select @#金牛座@#,4,20union all select @#双子座@#,5,21union all select @#巨蟹座@#,6,22union all select @#狮子座@#,7,23union all select @#处女座@#,8,23union all select @#天秤座@#,9,23union all select @#天蝎座@#,10,24union all select @#射手座@#,11,22union all select @#魔羯座@#,12,22) starswhere @ >= dateadd(month,[month] - 1,dateadd(year,year(@) - year(0),0)) + [day] - 1))end

原文转自：http://www.ltesting.net