T-SQL: 15 个与日期时间相关的自定义函数(UDF),周日作为周的最后一天,均不受 @@DateFirst、语言版

/*
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())
begin
return datediff(year,@StartDate,@EndDate)
       - case when datediff(day,dateadd(year,datediff(year,@StartDate,@EndDate),@StartDate),@EndDate) >= 0
                   then 0
              else
                   1
         end
end

go

create function udf_DaysOfYearByDate(@Date datetime)
RETURNS integer
-- 返回年的天数 可判断 平(365)、润(366) 年
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
-- 返回年的天数 可判断 平(365)、润(366) 年
begin
return 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 的十二点
as
begin
return 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)) --下午归到 十二点
       end
end

go

create function udf_WeekDiff(@StartDate datetime,@EndDate datetime)
returns integer
-- 返回 [@StartDate , @EndDate] 之间周数 周日是当周的最后一天
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)
-- 返回 @Date 是所在月的第几周 周日是当周的最后一天
returns integer
as
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
                 end
               ) + 1
end

go

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

go

create function udf_WeekOfYear(@Date datetime)
-- 返回 @Date 是所在年的第几周 周日是当周的最后一天
returns int
as
begin
return 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
               ) + 1
end

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: Sunday
return 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 datetime
declare @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 返回 1
begin
--1: Monday , ... ,7: Sunday
return (@@Datefirst + datepart(weekday,@Date)) % 7
       + case when (@@Datefirst + datepart(weekday,@Date)) % 7 < 2
                   then 6
              else
                   -1
         end
end

go

create function udf_NextWorkDate(@Date datetime)
returns datetime
-- 返回 @Date 的下一个工作日
begin
/*
declare @i int
set @i = 3
declare @Date datetime
set @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)
       end
end

go

create function udf_PreviousWorkDate(@Date datetime)
returns datetime
-- 返回 @Date 的上一个工作日
begin
/*
declare @i int
set @i = 3
declare @Date datetime
set @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)
       end
end

go

create function udf_WorkDateAdd(@i integer,@Date datetime)
returns datetime
-- 返回 @Date 加上一段 @i 个工作日的新值
begin
declare @ int
set @ = 0
while @ < 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
end
return @Date
end

go

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

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