declare @d datetime set datefirst 1 set @d=dateadd(dd,-1,dateadd(mm,1,convert(varchar(7),getdate(),120)+'-01')) select 星期一=dateadd(dd,-6,dateadd(dd,7-datepart(dw,@d),@d)), 星期二=dateadd(dd,-5,dateadd(dd,7-datepart(dw,@d),@d))
set nocount on if object_id('tb') is not null drop table tb go create table tb(Rq datetime) declare @date datetime declare @d datetime set @d=convert(varchar(7),getdate(),120)+'-01' set @date=getdate() while @D<=convert(varchar(7),getdate(),120)+'-'+cast( DAY(dateadd(day,-1,convert(char(07),dateadd(month,1,@date),120)+'-01')) as varchar(2)) begin insert tb select @d set @d=dateadd(dd,1,@d) end select top 1 rq 星期一 from tb where datepart(weekday,rq)=2 order by rq desc select top 1 rq 星期二 from tb where datepart(weekday,rq)=3 order by rq desc星期一 ------------------------------------------------------ 2008-09-29 00:00:00.000星期二 ------------------------------------------------------ 2008-09-30 00:00:00.000
你算错了 DECLARE @t datetime DECLARE @now datetimeSET @now = '2008-3-1'SET @t = DATEADD(DAY,-1,DATEADD(MONTH,1,CONVERT(datetime,CONVERT(varchar(7),@now,120) + '-01',120))) SELECT [星期1] = DATEADD(DAY,CASE DATEPART(DW,@t) WHEN 2 THEN 0 WHEN 1 THEN -6 ELSE 2-DATEPART(DW,@t) END,@t), [星期2] = DATEADD(DAY,CASE DATEPART(DW,@t) WHEN 3 THEN 0 WHEN 1 THEN -5 WHEN 2 THEN -6 ELSE 3-DATEPART(DW,@t) END,@t)
declare @dt table (id int identity(1,1), dt datetime) insert into @dt select top 31 dt=dateadd(month,datediff(month,0,getdate()),0) from sysobjects update @dt set dt = dt+id-1select dt = max(dt) from ( select dt, wd = datepart(weekday,dt) from @dt where month(dt) = month((select dt from @dt where id=1)) and datepart(weekday,dt) in (2,3) ) as a group by wd 2008-09-29 00:00:00.000 2008-09-30 00:00:00.000
DECLARE @T TABLE(D SMALLDATETIME)DECLARE @DATE SMALLDATETIME DECLARE @WD INT SET DATEFIRST 1 SET @WD=0 SET @DATE=DATEADD(D,-1,CONVERT(VARCHAR(8),DATEADD(M,1,GETDATE()),120)+'01')WHILE @WD<7 BEGIN INSERT @T SELECT DATEADD(D,-@WD,@DATE) SET @WD=@WD+1 ENDSELECT *,DATEPART(DW,D) AS DW FROM @T ORDER BY D /* D DW ------------------------------------------------------ ----------- 2008-09-24 00:00:00 3 2008-09-25 00:00:00 4 2008-09-26 00:00:00 5 2008-09-27 00:00:00 6 2008-09-28 00:00:00 7 2008-09-29 00:00:00 1 2008-09-30 00:00:00 2(7 row(s) affected) */
有道理,那就应该这样: declare @d datetime set datefirst 1 set @d=dateadd(dd,-1,dateadd(mm,1,convert(varchar(7),getdate(),120)+'-01')) select dateadd(dd,-6,dateadd(dd,7-datepart(dw,@d),@d)), 星期二=case when datepart(dw,@d)>1 then dateadd(dd,-5,dateadd(dd,7-datepart(dw,@d),@d)) else dateadd(dd,-12,dateadd(dd,7-datepart(dw,@d),@d)) end
DECLARE @DT DATETIME SET @DT=GETDATE() SET @DT=DATEADD(MM,1,@DT)SELECT 本月最后一个星期一=DATEADD(WK,DATEDIFF(WK,7,DATEADD(DD,6-DATEPART(DAY,@DT),@DT)),0), 本月最后一个星期二=DATEADD(WK,DATEDIFF(WK,7,DATEADD(DD,6-DATEPART(DAY,@DT),@DT)),1)/* 本月最后一个星期一 本月最后一个星期二 ------------------------------------------------------ ------------------------------------------------------ 2008-09-29 00:00:00.000 2008-09-30 00:00:00.000(所影响的行数为 1 行) */
declare @d datetime set datefirst 1 set @d=dateadd(dd,-1,dateadd(mm,1,convert(varchar(7),getdate(),120)+'-01')) select 星期一=dateadd(dd,-6,dateadd(dd,7-datepart(dw,@d),@d)), 星期二=dateadd(dd,-5,dateadd(dd,7-datepart(dw,@d),@d))
declare @d datetime
set datefirst 1
set @d=dateadd(dd,-1,dateadd(mm,1,convert(varchar(7),getdate(),120)+'-01'))
select 星期一=dateadd(dd,-6,dateadd(dd,7-datepart(dw,@d),@d)),
星期二=dateadd(dd,-5,dateadd(dd,7-datepart(dw,@d),@d))
if object_id('tb') is not null
drop table tb
go
create table tb(Rq datetime)
declare @date datetime
declare @d datetime
set @d=convert(varchar(7),getdate(),120)+'-01'
set @date=getdate()
while @D<=convert(varchar(7),getdate(),120)+'-'+cast( DAY(dateadd(day,-1,convert(char(07),dateadd(month,1,@date),120)+'-01')) as varchar(2))
begin
insert tb select @d
set @d=dateadd(dd,1,@d)
end
select top 1 rq 星期一 from tb where datepart(weekday,rq)=2 order by rq desc
select top 1 rq 星期二 from tb where datepart(weekday,rq)=3 order by rq desc星期一
------------------------------------------------------
2008-09-29 00:00:00.000星期二
------------------------------------------------------
2008-09-30 00:00:00.000
DECLARE @t datetime
DECLARE @now datetimeSET @now = '2008-3-1'SET @t = DATEADD(DAY,-1,DATEADD(MONTH,1,CONVERT(datetime,CONVERT(varchar(7),@now,120) + '-01',120)))
SELECT
[星期1] = DATEADD(DAY,CASE DATEPART(DW,@t)
WHEN 2 THEN 0
WHEN 1 THEN -6
ELSE 2-DATEPART(DW,@t) END,@t),
[星期2] = DATEADD(DAY,CASE DATEPART(DW,@t)
WHEN 3 THEN 0
WHEN 1 THEN -5
WHEN 2 THEN -6
ELSE 3-DATEPART(DW,@t) END,@t)
declare @dt table (id int identity(1,1), dt datetime)
insert into @dt
select top 31 dt=dateadd(month,datediff(month,0,getdate()),0) from sysobjects
update @dt set dt = dt+id-1select dt = max(dt)
from (
select dt, wd = datepart(weekday,dt)
from @dt
where month(dt) = month((select dt from @dt where id=1))
and datepart(weekday,dt) in (2,3)
) as a
group by wd
2008-09-29 00:00:00.000
2008-09-30 00:00:00.000
DECLARE @T TABLE(D SMALLDATETIME)DECLARE @DATE SMALLDATETIME
DECLARE @WD INT
SET DATEFIRST 1
SET @WD=0
SET @DATE=DATEADD(D,-1,CONVERT(VARCHAR(8),DATEADD(M,1,GETDATE()),120)+'01')WHILE @WD<7
BEGIN
INSERT @T SELECT DATEADD(D,-@WD,@DATE)
SET @WD=@WD+1
ENDSELECT *,DATEPART(DW,D) AS DW FROM @T ORDER BY D
/*
D DW
------------------------------------------------------ -----------
2008-09-24 00:00:00 3
2008-09-25 00:00:00 4
2008-09-26 00:00:00 5
2008-09-27 00:00:00 6
2008-09-28 00:00:00 7
2008-09-29 00:00:00 1
2008-09-30 00:00:00 2(7 row(s) affected)
*/
有道理,那就应该这样:
declare @d datetime
set datefirst 1
set @d=dateadd(dd,-1,dateadd(mm,1,convert(varchar(7),getdate(),120)+'-01'))
select dateadd(dd,-6,dateadd(dd,7-datepart(dw,@d),@d)),
星期二=case when datepart(dw,@d)>1 then
dateadd(dd,-5,dateadd(dd,7-datepart(dw,@d),@d))
else dateadd(dd,-12,dateadd(dd,7-datepart(dw,@d),@d)) end
SET @DT=GETDATE()
SET @DT=DATEADD(MM,1,@DT)SELECT 本月最后一个星期一=DATEADD(WK,DATEDIFF(WK,7,DATEADD(DD,6-DATEPART(DAY,@DT),@DT)),0),
本月最后一个星期二=DATEADD(WK,DATEDIFF(WK,7,DATEADD(DD,6-DATEPART(DAY,@DT),@DT)),1)/*
本月最后一个星期一 本月最后一个星期二
------------------------------------------------------ ------------------------------------------------------
2008-09-29 00:00:00.000 2008-09-30 00:00:00.000(所影响的行数为 1 行)
*/
set datefirst 1
set @d=dateadd(dd,-1,dateadd(mm,1,convert(varchar(7),getdate(),120)+'-01'))
select 星期一=dateadd(dd,-6,dateadd(dd,7-datepart(dw,@d),@d)),
星期二=dateadd(dd,-5,dateadd(dd,7-datepart(dw,@d),@d))