计算日期

[navy887]

表table有周期类型和频率两个字段
周期类型 频率
每周 星期1
每周 星期3
每周 星期5

需求：
根据当前日期取出下一个频率的日期
比如现在是2010年12月31号，星期5，则需要得到下个周1的日期时2011年1月3日

[华夏小卒]

草根大大，好久不见

[navy887]

握个手，帮我想想，呵呵。。

[华夏小卒]

--> 测试数据: #tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go 
create table #tb (周期类型 varchar(4),频率 varchar(5))
insert into #tb
select '每周','星期1' union all
select '每周','星期3' union all
select '每周','星期5'

set datefirst 1

select *,
	日期=dateadd(day,cast(replace(频率,'星期','')as int)+7-datepart(weekday,getdate()),getdate()) 
from #tb


周期类型 频率    日期
---- ----- -----------------------
每周   星期1   2011-01-03 11:04:34.857
每周   星期3   2011-01-05 11:04:34.857
每周   星期5   2011-01-07 11:04:34.857

(3 行受影响)

[dawugui]

[Quote=引用楼主 navy887 的回复:]
表table有周期类型和频率两个字段
周期类型 频率
每周 星期1
每周 星期3
每周 星期5

需求：
根据当前日期取出下一个频率的日期
比如现在是2010年12月31号，星期5，则需要得到下个周1的日期时2011年1月3日
[/Quote]
罗列出今天之后的七天,然后用case when来判断?

[dawugui]

declare @sdate datetime
declare @edate datetime
set @sdate = cast('2010-12-31' as datetime) + 1
set @edate = @sdate + 7 + 1

select * from
(
select 
    dateadd(dd,num,@sdate) dt , datepart(weekday,dateadd(dd,num,@sdate)) [weekday]
from 
    (select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a
where
    dateadd(dd,num,@sdate)<=@edate
) t
where (datepart(weekday , @sdate) in (1,6,7) and datepart(weekday , dt ) = 2) or
(datepart(weekday , @sdate) in (2,3) and datepart(weekday , dt ) = 4) or
(datepart(weekday , @sdate) in (4,5) and datepart(weekday , dt ) = 6)

/*
dt                                                     weekday     
------------------------------------------------------ ----------- 
2011-01-03 00:00:00.000                                2

（所影响的行数为 1 行）
*/

[playwarcraft]

这个只能枚举了，
可能有 每日，每周，每月，每季，每年之类的吧。。。。

[dawugui]

declare @sdate datetime
declare @edate datetime
set @sdate = cast('2010-12-31' as datetime) + 1
set @edate = @sdate + 5 + 1   
-- 排除当天，应该六天就够了，如果不对，自己更改更改一下，或用select top 1 * from ()t order by dt

select * from
(
select 
    dateadd(dd,num,@sdate) dt , datepart(weekday,dateadd(dd,num,@sdate)) [weekday]
from 
    (select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a
where
    dateadd(dd,num,@sdate)<=@edate
) t
where (datepart(weekday , @sdate) in (1,6,7) and datepart(weekday , dt ) = 2) or
(datepart(weekday , @sdate) in (2,3) and datepart(weekday , dt ) = 4) or
(datepart(weekday , @sdate) in (4,5) and datepart(weekday , dt ) = 6)

/*
dt                                                     weekday     
------------------------------------------------------ ----------- 
2011-01-03 00:00:00.000                                2

（所影响的行数为 1 行）
*/

[快溜]

select dateadd(day,cast((case when datepart(weekday,getdate())=5 then 3 else 1 end) as int),getdate())

[playwarcraft]

create table T(zq nvarchar(10),rate nvarchar(10))

insert into T  select N'每周', N'星期1'
insert into T  select N'每周', N'星期4'
insert into T  select N'每月', N'每月10号'
insert into T  select N'每年', N'1-5'

GO

select convert(char(10),min(date),120) as 'Next Day'
 from
(
select N'每日' as zq,dateadd(day,1,getdate()) as date
from T
where zq=N'每日'
union all
select N'每周',dateadd(day,number,getdate())
from master..spt_values 
where type='p'
and number between 1 and 14 
and datepart(weekday,dateadd(day,number,getdate())) in 
    (select convert(int,replace(rate,N'星期',''))+1 from T where zq=N'每周')
union all
select N'每月',dateadd(day,number,getdate())
from master..spt_values
where type='p'
and number between 1 and 62
and Rtrim(day(dateadd(day,number,getdate()))) in
    (select replace(replace(rate,N'每月',''),N'号','')
     from T where zq=N'每月'
    )
union all
select N'每年',case when convert(datetime ,Rtrim(year(getdate()))+'-'+rate)>getdate() 
               then convert(datetime ,Rtrim(year(getdate()))+'-'+rate)
               else convert(datetime ,Rtrim(year(getdate())+1)+'-'+rate)
               end
from T
where zq=N'每年'
) A
where date>getdate()

/*
Next Day
------------------
2011-01-03
*/
GO
drop table T