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

/*
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)
s eger
-- 返回精确年龄 select dbo.udf_GetAge(\'1949-10-01\',getdate)
begin
datedf(year,@StartDate,@EndDate)
- when datedf(day,dateadd(year,datedf(year,@StartDate,@EndDate),@StartDate),@EndDate) >= 0
then 0

1
end
end

go

create function udf_DaysOfYearByDate(@Date datetime)
RETURNS eger
-- 返回年天数 可判断 平(365)、润(366) 年
begin
datedf(day,dateadd(year,datedf(year,0,@Date),0),dateadd(year,datedf(year,0,@Date )+1,0))
end

go

create function udf_DaysOfYear(@Year eger)
RETURNS eger
-- 返回年天数 可判断 平(365)、润(366) 年
begin
datedf(day,dateadd(year,@year - year(0),0),dateadd(year,@year - year(0) + 1,0))
end

go

create function udf_HalfDay(@Date datetime)
s datetime
-- 返回 @Date 是 上午 返回 @Date 零点,@Date 是 下午 返回 @Date 十 2点
as
begin
when datepart(hour,@Date) < 12
then dateadd(day,datedf(day,0,@Date),0) --上午归到 零点

dateadd(hour,12,dateadd(day,datedf(day,0,@Date),0)) --下午归到 十 2点
end
end

go

create function udf_WeekDf(@StartDate datetime,@EndDate datetime)
s eger
-- 返回 [@StartDate , @EndDate] 的间周数 周日是当周最后天
begin
datedf(week,@StartDate,@EndDate) -- + 1
+ when (@@Datefirst + datepart(weekday,@StartDate)) % 7 = 1
then 1

0
end
- when (@@Datefirst + datepart(weekday,@EndDate)) % 7 = 1
then 1
0
end
end

go

create function udf_WeekOfMonth(@Date datetime)
-- 返回 @Date 是所在月第几周 周日是当周最后天
s eger
as
begin
datedf(week
, when (@@Datefirst + datepart(weekday,dateadd(month,datedf(month,0,@Date),0))) % 7 = 1
then dateadd(month,datedf(month,0,@Date),0) - 1

dateadd(month,datedf(month,0,@Date),0)
end
, when (@@Datefirst + datepart(weekday,@Date)) % 7 = 1
then @Date-1
@Date
end


) + 1
end

go

create function udf_WeekOfQuarter(@Date datetime)
-- 返回 @Date 是所在季度第几周 周日是当周最后天
s
as
begin
datedf(week
, when (@@Datefirst + datepart(weekday,dateadd(Quarter,datedf(Quarter,0,@Date),0))) % 7 = 1
then dateadd(Quarter,datedf(Quarter,0,@Date),0) - 1

dateadd(Quarter,datedf(Quarter,0,@Date),0)
end
, when (@@Datefirst + datepart(weekday,@Date)) % 7 = 1
then @Date - 1

@Date
end
) + 1
end

go

create function udf_WeekOfYear(@Date datetime)
-- 返回 @Date 是所在年第几周 周日是当周最后天
s
as
begin
datedf(week
, when (@@Datefirst + datepart(weekday,dateadd(day,0,datedf(day,0,dateadd(year,datedf(year,0,@Date),0))))) % 7 = 1
then dateadd(day,-1,dateadd(day,0,datedf(day,0,dateadd(year,datedf(year,0,@Date),0))))

dateadd(day,0,datedf(day,0,dateadd(year,datedf(year,0,@Date),0))) --date 所在年第天 即: 月号
end
, when (@@Datefirst + datepart(weekday,@Date)) % 7 = 1
then dateadd(day,-1,@Date)

@Date
end
) + 1
end

go

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



1
end
when (@@Datefirst + datepart(weekday,@Date)) % 7 = 1 --周日( 7)
then when @ between 1 and 6
then @ - 7

0
end
when (@@Datefirst + datepart(weekday,@Date)) % 7 between 2 and 6 --周至周 5
then when @ between 1 and 6
then @ + 1 - (@@Datefirst + datepart(weekday,@Date)) % 7

8 - (@@Datefirst + datepart(weekday,@Date)) % 7
end
end
,@Date)
end

go

create function udf_WeekdayDf(@Weekday eger,@StartDate datetime,@EndDate datetime)
s eger
-- -- 返回 [@StartDate , @EndDate] 的间周 到 周日个数 周日是当周最后天
begin
-- @Weekday: 1: Monday , ... ,7: Sunday
datedf(week,@StartDate,@EndDate)
+ when (@@Datefirst + datepart(weekday,@StartDate)) % 7
+ when (@@Datefirst + datepart(weekday,@StartDate)) % 7 = 0
then 7

0
end > @Weekday % 7 + 1
then 0
1
end
- when (@@Datefirst + datepart(weekday,@EndDate)) % 7


+ when (@@Datefirst + datepart(weekday,@EndDate)) % 7 = 0
then 7
0
end >= @Weekday % 7 + 1
then
0

1
end
/* test:

declare @b datetime
declare @e datetime

@b = \'2004-01-29\'
@e = \'2004-09-05\'

select @b as BeginDate ,@e as EndDate
,dbo.udf_WeekdayDf(1,@b,@e) as CountOfMonday
,dbo.udf_WeekdayDf(2,@b,@e) as CountOfTuesday
,dbo.udf_WeekdayDf(3,@b,@e) as CountOfWednesday
,dbo.udf_WeekdayDf(4,@b,@e) as CountOfThursday
,dbo.udf_WeekdayDf(5,@b,@e) as CountOfFriday
,dbo.udf_WeekdayDf(6,@b,@e) as CountOfSaturday
,dbo.udf_WeekdayDf(7,@b,@e) as CountOfSunday
*/
end

go

create function udf_WeekdayID(@Date datetime)
s eger
-- 返回 @Date 是 Monday 返回 1, ... ,是 Sunday 返回 1
begin
--1: Monday , ... ,7: Sunday
(@@Datefirst + datepart(weekday,@Date)) % 7
+ when (@@Datefirst + datepart(weekday,@Date)) % 7 < 2
then 6

-1
end
end

go

create function udf_NextWorkDate(@Date datetime)
s datetime
-- 返回 @Date 下个工作日
begin
/*
declare @i
@i = 3
declare @Date datetime
@Date = \'2005-01-02\'
-- */
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)

dateadd(day,1,@Date)
end
end

go

create function udf_PreviousWorkDate(@Date datetime)
s datetime
-- 返回 @Date 上个工作日
begin
/*
declare @i
@i = 3
declare @Date datetime
@Date = \'2005-01-02\'
-- */
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)

dateadd(day,-1,@Date)
end
end

go

create function udf_WorkDateAdd(@i eger,@Date datetime)
s datetime
-- 返回 @Date 加上段 @i 个工作日新值
begin
declare @
@ = 0
while @ < abs(@i)
begin
@Date = when @i >= 0
then --dbo.udf_nextworkdate(@Date)
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)

dateadd(day,1,@Date)
end

--dbo.udf_previousworkdate(@Date)
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)

dateadd(day,-1,@Date)
end
end
@ = @ + 1
end
@Date
end

go

create function udf_GetStar (@ datetime)
RETURNS varchar(100)
-- 返回日期所属星座
BEGIN
RETURN
(
--declare @ datetime
-- @ = 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

延伸阅读

• 2010-12-9-- php常用函数,PHP项目开发中最常用的自定义函数整理
• 2010-12-9-- 自定义函数,PHP自定义函数收代码
• 2010-12-15-- 自定义函数,PHP项目中会经常用到的自定义函数收集
• 2010-12-9-- 自定义函数,asp 自定义分段函数/求第N名成绩
• 2010-12-9-- js自定义函数,js RuntimeObject（） 获取ie里面自定义函数或者属性的集合
• 2011-4-26-- 自定义函数,mysql建立自定义函数的问题
• 2011-4-13-- 自定义函数,Hive中实现自定义函数UDF及打包
• 2008-12-26-- 自定义工具条:用VB制作IE工具条自定义按钮
• 2009-9-12-- mysql自定义函数:MYSQL利用自定义函数提升权限
• 2009-2-3-- excel自定义函数:Excel 2007 自定义菜单技术(1)