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

最新推荐文章于 2022-07-07 00:13:58 发布

原创最新推荐文章于 2022-07-07 00:13:58 发布 · 2k 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

DataBase

本文介绍了 17 个 T-SQL 中与日期时间相关的自定义函数（UDF），这些函数不受 @@DateFirst 和语言版本影响，包括计算精确年龄、判断平闰年、计算周数等功能，还给出了部分函数的使用示例及周历查询代码。

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

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

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

create function udf_HalfDay(@Date datetime)
returns datetime
-- 返回 @Date 是上午返回 @Date 的零点,@Date 是下午返回 @Date 的十二点
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

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

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

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

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

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

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

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

create function udf_DayOfQuarter(@Date datetime)
-- 返回 @Date 是所在季度的第几天
returns integer
as
begin
/*
declare @date datetime
set @date = '2004-4-1'
--*/
return datediff(day
               ,dateadd(Quarter,datediff(Quarter,0,@Date),0)
               ,@Date
               ) + 1
end

create function udf_DaysOfQuarterByDate(@Date datetime)
-- 返回 @Date 所在季度的天数
returns integer
begin
/*
declare @date datetime
set @date = '2004-4-1'
--*/
return datediff(day
               ,dateadd(Quarter,datediff(Quarter,0,@Date),0)
               ,dateadd(Quarter,datediff(Quarter,0,@Date) + 1,0)
               )
end

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

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

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 @date + 3 --dateadd(day,3,@Date)
                                   when (@@Datefirst + datepart(weekday,@Date)) % 7 = 0 -- saturday
                                        then @date + 2 --dateadd(day,2,@Date)
                                   else
                                         @date + 1 --dateadd(day,1,@Date)
                              end
                    else
                         --dbo.udf_previousworkdate(@Date)
                         case when (@@Datefirst + datepart(weekday,@Date)) % 7 = 2 -- Monday
                                   then @date - 3 --dateadd(day,-3,@Date)
                              when (@@Datefirst + datepart(weekday,@Date)) % 7 = 1 -- Sunday
                                   then @date - 2 --dateadd(day,-2,@Date)
                              else
                                    @date - 1 --dateadd(day,-1,@Date)
                         end
               end
               set @ = @ + 1
end
return @Date
end

create function udf_GetStar (@ datetime)
returns varchar(100)
-- 返回日期所属星座,如果有静态的星座对照码表直接在查询中 join 效率相对更高
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
go
use northwind
select a.birthdate,b.star
from employees a
left join
(
select a.*,isnull(b.month,12) as m,isnull(b.day,31) as d
from stars a
left join stars b
on a.month * 40 + a.day < b.month * 40 + b.day
and b.month * 40 + b.day = (select min(month * 40 + day) from stars where month * 40 + day > a.month * 40 + a.day)
) b
on month(a.birthdate) * 40 + day(a.birthdate) >= b.month * 40 + b.day
and month(a.birthdate) * 40 + day(a.birthdate) < b.m * 40 + b.d

select e.birthdate,a.star
from employees e
left join stars a
on month(e.birthdate) * 40 + day(e.birthdate) >= a.month * 40 + a.day
left join stars b
on a.month * 40 + a.day < b.month * 40 + b.day
and b.month * 40 + b.day = (select min(month * 40 + day) from stars where month * 40 + day > a.month * 40 + a.day)
where month(e.birthdate) * 40 + day(e.birthdate) < isnull(b.month * 40 + b.day,999)

select *
from stars a
left join stars b
on a.month * 40 + a.day < b.month * 40 + b.day
and b.month * 40 + b.day = (select min(month * 40 + day) from stars where month * 40 + day > a.month * 40 + a.day)
go
--周历
declare @ datetime
set @ = getdate()

select @ +
case when (@@Datefirst + datepart(weekday,@)) % 7 = 0 --周六
          then -5
     when (@@Datefirst + datepart(weekday,@)) % 7 = 1 --周日(七)
          then -6
     when (@@Datefirst + datepart(weekday,@)) % 7 between 2 and 6 --周一至周五
          then 2 - (@@Datefirst + datepart(weekday,@)) % 7
end + N.i
from
(
select 0 as i
union all select 1 union all select 2 union all select 3
union all select 4 union all select 5 union all select 6
union all select 7
) N

标签

#语言 #date #integer #function #go