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))

评论