15 个与日期时间相关的精典语句函数

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()) 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 ) 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 /* --周日算作(上一)周的最后一天 当 @ = 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 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

修改 删除 举报 引用 回复

加为好友 发送私信 在线聊天 hery2002 苦*行*僧 等级:

发表于：2008-08-03 21:48:262楼 得分:0 SQL code 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 @ = 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 SQL Server 日期算法 一周的第一天 select @@DATEFIRST 一个月的第一天 select dateadd (mm, datediff (mm, 0 , getdate ()), 0 ) 本周的星期一 select dateadd (wk, datediff (wk, 0 , getdate ()), 0 ) 一年的第一天 SELECT DATEADD (yy, DATEDIFF (yy, 0 , getdate ()), 0 ) 季度的第一天 SELECT DATEADD (qq, DATEDIFF (qq, 0 , getdate ()), 0 ) 当天的零时 SELECT DATEADD (dd, DATEDIFF (dd, 0 , getdate ()), 0 ) 上个月的最后一天 ：本月第一天减2ms. SELECT dateadd (ms, - 2 , DATEADD (mm, DATEDIFF (mm, 0 , getdate ()), 0 )) 本月的最后一天 SELECT dateadd (ms, - 2 , DATEADD (mm, DATEDIFF (m, 0 , getdate ()) + 1 , 0 )) 本月的第一个星期一 去掉时分秒 DATEADD ( day , DATEDIFF ( day , 0 , getdate ()), 0 ) 显示星期几 select datename (weekday, getdate ()) 如何取得某个月的天数 SELECT Day ( dateadd (ms, - 3 , DATEADD (mm, DATEDIFF (m, 0 , getdate ()) + 1 , 0 ))) 判断是否闰年： SELECT case day ( dateadd (mm, 2 , dateadd (ms, - 3 , DATEADD (yy, DATEDIFF (yy, 0 , getdate ()), 0 )))) when 28 then ' 平年 ' else ' 闰年 ' end 一个季度多少天 declare @m tinyint , @time smalldatetime select @m = month ( getdate ()) select @m = case when @m between 1 and 3 then 1 when @m between 4 and 6 then 4 when @m between 7 and 9 then 7 else 10 end select @time = datename ( year , getdate ()) + ' - ' + convert ( varchar ( 10 ), @m ) + ' -01 ' select datediff ( day , @time , dateadd (mm, 3 , @time ))