create function udf_PreviousWorkDate(@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 = 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)        endendgocreate function udf_WorkDateAdd(@i integer,@Date datetime)returns datetime-- 返回 @Date 加上一段 @i 个工作日的新值begindeclare @ intset @ = 0while @ = 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 endreturn @Dateendgocreate function udf_GetStar (@ datetime)RETURNS varchar(100)-- 返回日期所属星座BEGINRETURN(--declare @ datetime--set @ = getdate()select max(star)from(select '魔羯座' as star,1 as [month],1 as [day]union all select '水瓶座',1,20union all select '双鱼座',2,19union all select '牡羊座',3,21union all select '金牛座',4,20union all select '双子座',5,21union all select '巨蟹座',6,22union all select '狮子座',7,23union all select '处女座',8,23union all select '天秤座',9,23union all select '天蝎座',10,24union all select '射手座',11,22union all select '魔羯座',12,22) starswhere 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,20union all select '双鱼座',2,19union all select '牡羊座',3,21union all select '金牛座',4,20union all select '双子座',5,21union all select '巨蟹座',6,22union all select '狮子座',7,23union all select '处女座',8,23union all select '天秤座',9,23union all select '天蝎座',10,24union all select '射手座',11,22union all select '魔羯座',12,22) starswhere @ >= dateadd(month,[month] - 1,dateadd(year,year(@) - year(0),0)) + [day] - 1))endSQL 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))

评论