创建博客 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

老头的博客

天上飞的果然是鸟人

 
 
 

日志

 
 

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

2010-03-19 15:43:51|  分类: 默认分类 |  标签: |举报 |字号 订阅

(@@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

  评论这张
 
阅读(414)| 评论(0)
|      
推荐 转载

历史上的今天

最近读者

热度

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2014