sql有关日期的实现

来源：互联网发布：数据之巅 pdf 编辑：程序博客网时间：2024/05/21 21:34

--************
--在两个日期范围里所跨越那几周返回如：1，2表是第一周和第二周，

declare@aatable(datedatetime,weekdaysint)
declare@iint
set@i=datediff(day,@bdate,@edate)
while(@i>=0)
begin
insert@aa
values(dateadd(day,@i,@bdate),datepart(week,dateadd(day,@i,@bdate)))
set@i=@i-1
end
selectweekdays
into#week
from@AAgroupbyweekdays

--************
--在日期范围里减去周六、周日的天数
createfunctiona(@Sdatedatetime,@Edatedatetime)
returns  int
as
begin
declare@aatable(datedatetime)
declare@iint
set@i=datediff(day,@Sdate,@Edate)
while(@i>=0)
begin
insert@aa
values(dateadd(day,@i,@Sdate))
set@i=@i-1
end
select@i=count(*) from@aawhere  datepart(weekday,date)notin(1,7)
return@i

end

--如：selectdbo.A('2004-10-01','2004-10-11')
--返回结果为7

--***********
--输入第几周得到此周的开始、结束日期
declare@FirstDayOfYeardatetime--年头
declare@FirstDayWeekOfYeardatetime--第一周的第一天
declare@BDatedatetime
declare@EDatedatetime

select@FirstDayOfYear=dateadd(yy,datediff(yy,0,getdate()),0)
select  @FirstDayWeekOfYear=@FirstDayOfYear -datepart(dw,@FirstDayOfYear)+1

select @EDate=dateadd(ww,@week,@FirstDayWeekOfYear-1)
select@BDate= dateadd(ww,-1,dateadd(dd,1,@EDate) )

SET@BDate=convert(datetime,convert(char(10),@BDate,101))
SET@EDate=convert(datetime,convert(char(10),@EDate,101))
<script type="text/javascript"></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>