22,207
社区成员
发帖
与我相关
我的任务
分享
declare @tdate datetime , @sdate datetime , @edate datetime ,@i int ,@s int
declare @t_temp table(c_sdate datetime,c_edate datetime,c_s int)
set @s = 1
set @i=1000
while @i>=0
begin
set @tdate='2011-1-1'
set @sdate=dateadd(day,cast(RAND()*365 as int),@tdate)
set @edate=dateadd(day,cast(RAND()*365 as int),@tdate)
if @sdate>@edate
select @tdate=@sdate , @sdate=@edate , @edate=@tdate
insert into @t_temp(c_sdate ,c_edate ,c_s)
select @sdate,@edate,@s
set @i=@i-1
end
select a.* from @t_temp as a
Declare @StartDate DateTime
Declare @EndDate DateTime
Set @StartDate='2008-5-7'
Set @EndDate='2008-5-31'
Select DateDiff(wk,@StartDate,@EndDate)+(Case When Datepart(dw,@StartDate)=1 Then 1 Else 0 End)
declare @startdate datetime
declare @enddate datetime
declare @i int
set @startdate = '2011-05-01'
set @enddate = '2011-05-22'
set @i = 1
select (datediff(day,@startdate,@enddate)
+ datepart(weekday,@startdate)
+ (7 - datepart(weekday,@enddate)))/7
+ (case
when datepart(weekday,@startdate) > @i then -1
else 0
end)
+ (case
when datepart(weekday,@enddate) < @i then -1
else 0
end)
declare @k int;set @k=3 -- 星期几
declare @i datetime;set @i='2011-03-01' --开始时间
declare @j datetime;set @j='2011-03-19' --结束时间
select case when @k>=case datepart(w,@i)-1 when 0 then 7 else datepart(w,@i)-1 end then (datediff(d,@i,@j)-(@k-case datepart(w,@i)-1 when 0 then 7 else datepart(w,@i)-1 end))/7+1 else (datediff(d,@i,@j)-(7-(case datepart(w,@i)-1 when 0 then 7 else datepart(w,@i)-1 end-@k)))/7+1 end
declare @k int;set @k=1 -- 星期几
declare @i datetime;set @i='2011-03-01' --开始时间
declare @j datetime;set @j='2011-05-08' --结束时间
select case when @k>=case datepart(w,@i)-1 when 0 then 7 else datepart(w,@i)-1 end then (datediff(d,@i,@j)-(@k-case datepart(w,@i)-1 when 0 then 7 else datepart(w,@i)-1 end))/7+1 else (datediff(d,@i,@j)-(7-(case datepart(w,@i)-1 when 0 then 7 else datepart(w,@i)-1 end-@k)))/7+1 end
/*
星期 个数
---- ----
1 9
2 10
3 10
4 10
5 10
6 10
7 10
*/
--原帖地址:http://blog.csdn.net/DengXingJie/archive/2011/02/25/6208762.aspx
/*
**********************************************
Program ID: FUN_GetDaysOfWeek
Purpose : 求某段时间内某一星期日期的天数
Author : Jesse
Date : 2011.02.19
**********************************************
*/
go
Create Function dbo.FUN_GetDaysOfWeek(
@DateS smalldatetime,--开始时间
@DateE smalldatetime,--结束
@WeekValue int) --星期的某一天:-周日、-周一...7-周六
Returns nvarchar(100)
As
Begin
declare
@sResult nvarchar(100), --结果描述
@nResult int, --结果天数
@nWeekValue int, --起始日期是星期几(1-周日、-周一...7-周六)
@nDays int, --时间段内的天数
@nBeforeDays int, --起始日期到第一次相符日期的天数
@sWeekName varchar(20), --所求日期的星期名称
@tmpDate smalldatetime
set @WeekValue=@WeekValue % 7
if @WeekValue=0
set @WeekValue=7
--如果起始日期大于终止日期,则对换
if @DateS>@DateE
select @tmpDate=@DateE,@DateE=@DateS,@DateS=@tmpDate
--取得起始日期的星期数,与设置无关(1-周日、-周一...7-周六)
select @nWeekValue=(Datepart(dw,@DateS)+(@@Datefirst %7))%7
if @nWeekValue=0
set @nWeekValue=7
set @sWeekName=DateName(dw,DateAdd(d,@WeekValue-@nWeekValue,@DateS))
if @WeekValue>=@nWeekValue
set @nBeforeDays=@WeekValue-@nWeekValue
else
set @nBeforeDays=7-abs(@WeekValue-@nWeekValue)
--取得时间段内的天数
select @nDays=Datediff(d,@DateS,@DateE)+1
if @nDays<@nBeforeDays
select @nResult=0
else
select @nResult=ceiling((@nDays-@nBeforeDays)/7.0)
set @sResult=N'日期区间:'+ convert(varchar(10),@DateS,120)+ N' 到' +convert(varchar(10),@DateE,120) +' 之间'
+@sWeekName +N' 共有'+cast(@nResult as varchar(100))+N' 天'
Return @sResult
end
go
--测试示例
select dbo.FUN_GetDaysOfWeek('2011-03-01','2011-05-31',1)
--运行结果
/*
日期区间:2011-03-01 到2011-05-31 之间Sunday 共有13 天
*/
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/maco_wang/archive/2011/03/20/6262935.aspx