T-SQL: 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 endendgocreate 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))endgocreate 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))endgocreate function udf_HalfDay(@Date datetime)returns datetime-- 返回 @Date 是 上午 返回 @Date 的零点,@Date 是 下午 返回 @Date 的十二点asbeginreturn case when datepart(hour,@Date) gocreate 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 endendgocreate 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 ) + 1endgocreate 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 ) + 1endgocreate 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 ) + 1endgocreate function udf_WeekDay(@ int,@Date datetime)returns datetime-- 返回 @Date 映射到 所在周的其他天 周日是当周的最后一天begin/*--周日算作(上一)周的最后一天 当 @ = 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)endgocreate 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 datetimeset @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*/endgocreate 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 gocreate 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) endendgo
评论