T-SQL: 15 个与日期时刻相干的自界说函数(UDF),周日作为周的最后一天,均不受 @@DateFirst、说话版
发布时间：2018-08-19 03:41:00 所属栏目：电商 来源：站长网
导读：/* T-SQL: 15 个与日期时刻相干的自界说函数(UDF),周日作为周的最后一天,均不受 @@DateFirst、说话版本影响 都是从老文章里网络或提炼出来的! 提醒: (@@Datefirst + datepart(weekday,@Date)) % 7 判定周几是最保险的! 与 @@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 （编辑：湖南网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
