全年的星期日编号

Question

如何把全年的星期日编号：比如 2009-1-4 是第一个星期日，11号是第二个，以此类推，12-27是最后一个（算出来）。
任何一年的都如此。

Answer 1

SET DATEFIRST 1
DECLARE @date DATETIME
SET @date='2009-01-01'
while @date<='2009-12-31'
begin
    if DATEPART(dw,@date)=7
    begin
        print convert(varchar(10),@date,120)
    end
    set @date=@date+1
end2009-01-04
2009-01-11
2009-01-18
2009-01-25
2009-02-01
2009-02-08
2009-02-15
2009-02-22
2009-03-01
2009-03-08
2009-03-15
2009-03-22
2009-03-29
2009-04-05
2009-04-12
2009-04-19
2009-04-26
2009-05-03
2009-05-10
2009-05-17
2009-05-24
2009-05-31
2009-06-07
2009-06-14
2009-06-21
2009-06-28
2009-07-05
2009-07-12
2009-07-19
2009-07-26
2009-08-02
2009-08-09
2009-08-16
2009-08-23
2009-08-30
2009-09-06
2009-09-13
2009-09-20
2009-09-27
2009-10-04
2009-10-11
2009-10-18
2009-10-25
2009-11-01
2009-11-08
2009-11-15
2009-11-22
2009-11-29
2009-12-06
2009-12-13
2009-12-20
2009-12-27

Answer 2

DECLARE @Date datetime
DECLARE @StartDate datetime
DECLARE @EndDate datetime
DECLARE @WeekDay int
DECLARE @i intSET DATEFIRST 7 --设置每周的第一天
SET @StartDate='2009-01-01' --统计的开始日期
SET @EndDate='2009-12-31' --统计的结束日期
SET @WeekDay=1 --根据实际的@@DATEFIRST而定，一般默认是，如@StartDate='2006-01-01'时候，@WeekDay=3表示星期二
SET @i=DATEPART(weekday,@StartDate)
IF(@i<=@WeekDay AND @i<7)
SET @i=@WeekDay-@i 
ELSE IF(@i<=@WeekDay AND @i=7)
SET @i=@i-@WeekDay
ELSE
SET @i=@@DATEFIRST-@i+@WeekDaySET @Date=DATEADD(day,@i,@StartDate)
SET @i=1
WHILE @Date<=@EndDate 
BEGIN
IF(@StartDate<=@Date) 
begin
PRINT convert(varchar,@i) +' - '+ CONVERT(nvarchar(10),@Date,121)
Set @i=@i+1
end
SET @Date=DATEADD(Week,1,@Date)
END
GO

Answer 3

declare @dt datetime
set @dt = '2009-3-12' -- 设定参数,任意日期declare @dt1 datetime set @dt1 = @dtset @dt1 = dateadd(year,datediff(year,0,@dt1),0) -- 当年第一天
set @dt1 = dateadd(week,datediff(week,0,@dt1),0)-1 -- 所在周日 （一周以周日为始）
while year(@dt1)<=year(@dt) begin
    if year(@dt1)=year(@dt)
        print convert(varchar,@dt1,102)
    
    set @dt1=@dt1+7 -- 跳过一周
end

Answer 4

一年中所有为星期二的日期 select dateadd(day,x,col),'星期二' from 
( 
select cast('2006-1-1' as datetime) as col 
)a cross join 
( 
SELECT  top 365 b8.i+b7.i + b6.i + b5.i + b4.i +b3.i +b2.i + b1.i + b0.i x 
FROM(SELECT 0 i UNION ALL SELECT 1) b0 
CROSS JOIN(SELECT 0 i UNION ALL SELECT 2) b1 
CROSS JOIN(SELECT 0 i UNION ALL SELECT 4) b2 
CROSS JOIN(SELECT 0 i UNION ALL SELECT 8) b3 
CROSS JOIN(SELECT 0 i UNION ALL SELECT 16) b4 
CROSS JOIN(SELECT 0 i UNION ALL SELECT 32) b5 
CROSS JOIN(SELECT 0 i UNION ALL SELECT 64) b6 
CROSS JOIN(SELECT 0 i UNION ALL SELECT 128) b7 
CROSS JOIN(SELECT 0 i UNION ALL SELECT 256) b8 
order by 1 
)b 
where datepart(dw,dateadd(day,x,col))=3

Answer 5

受小F启发:
declare @parmdt datetime set @parmdt = '2009-01-01'select top 366 id=identity(int,0,1) 
into #t1
from sysobjects a,sysobjects bselect dt = @parmdt+id
from #t1
where datepart(weekday,@parmdt+id)=1drop table #t1

Answer 6

奇怪，为什么我的日期和你的的错一天呢，
SET DATEFIRST 1
select DATEPART(dw,‘2009-01-01’) 
--结果是5 即周五
而我电脑上日期看到的2009-01-01 是周四，怎么错一天啊，这个应该在哪儿设置

Answer 7

set datefirst 1select dateadd(dd,7*number,'2009-1-4') from  master.dbo.spt_values
where type = 'p' and number<365/7
/*
-----------------------
2009-01-04 00:00:00.000
2009-01-11 00:00:00.000
2009-01-18 00:00:00.000
2009-01-25 00:00:00.000
2009-02-01 00:00:00.000
2009-02-08 00:00:00.000
2009-02-15 00:00:00.000
2009-02-22 00:00:00.000
2009-03-01 00:00:00.000
2009-03-08 00:00:00.000
2009-03-15 00:00:00.000
2009-03-22 00:00:00.000
2009-03-29 00:00:00.000
2009-04-05 00:00:00.000
2009-04-12 00:00:00.000
2009-04-19 00:00:00.000
2009-04-26 00:00:00.000
2009-05-03 00:00:00.000
2009-05-10 00:00:00.000
2009-05-17 00:00:00.000
2009-05-24 00:00:00.000
2009-05-31 00:00:00.000
2009-06-07 00:00:00.000
2009-06-14 00:00:00.000
2009-06-21 00:00:00.000
2009-06-28 00:00:00.000
2009-07-05 00:00:00.000
2009-07-12 00:00:00.000
2009-07-19 00:00:00.000
2009-07-26 00:00:00.000
2009-08-02 00:00:00.000
2009-08-09 00:00:00.000
2009-08-16 00:00:00.000
2009-08-23 00:00:00.000
2009-08-30 00:00:00.000
2009-09-06 00:00:00.000
2009-09-13 00:00:00.000
2009-09-20 00:00:00.000
2009-09-27 00:00:00.000
2009-10-04 00:00:00.000
2009-10-11 00:00:00.000
2009-10-18 00:00:00.000
2009-10-25 00:00:00.000
2009-11-01 00:00:00.000
2009-11-08 00:00:00.000
2009-11-15 00:00:00.000
2009-11-22 00:00:00.000
2009-11-29 00:00:00.000
2009-12-06 00:00:00.000
2009-12-13 00:00:00.000
2009-12-20 00:00:00.000
2009-12-27 00:00:00.000(52 row(s) affected)
*/

Answer 8

create function fun_sunday()
returns @temp table(id int identity(1,1),sunday datetime)
as
begin
declare @t datetime
set @t='2009-1-1'

while(@t<'2010-1-1')
begin
if(datename(weekday,@t)='星期日')
begin
insert @temp(sunday)
  select @t

set @t=dateadd(day,7,@t)
continue
end
else
set @t=dateadd(day,1,@t)
end

return
endselect * from dbo.fun_sunday()/*
id          sunday                                                 
----------- ------------------------------------------------------ 
1           2009-01-04 00:00:00.000
2           2009-01-11 00:00:00.000
3           2009-01-18 00:00:00.000
4           2009-01-25 00:00:00.000
5           2009-02-01 00:00:00.000
6           2009-02-08 00:00:00.000
7           2009-02-15 00:00:00.000
8           2009-02-22 00:00:00.000
9           2009-03-01 00:00:00.000
10          2009-03-08 00:00:00.000
11          2009-03-15 00:00:00.000
12          2009-03-22 00:00:00.000
13          2009-03-29 00:00:00.000
14          2009-04-05 00:00:00.000
15          2009-04-12 00:00:00.000
16          2009-04-19 00:00:00.000
17          2009-04-26 00:00:00.000
18          2009-05-03 00:00:00.000
19          2009-05-10 00:00:00.000
20          2009-05-17 00:00:00.000
21          2009-05-24 00:00:00.000
22          2009-05-31 00:00:00.000
23          2009-06-07 00:00:00.000
24          2009-06-14 00:00:00.000
25          2009-06-21 00:00:00.000
26          2009-06-28 00:00:00.000
27          2009-07-05 00:00:00.000
28          2009-07-12 00:00:00.000
29          2009-07-19 00:00:00.000
30          2009-07-26 00:00:00.000
31          2009-08-02 00:00:00.000
32          2009-08-09 00:00:00.000
33          2009-08-16 00:00:00.000
34          2009-08-23 00:00:00.000
35          2009-08-30 00:00:00.000
36          2009-09-06 00:00:00.000
37          2009-09-13 00:00:00.000
38          2009-09-20 00:00:00.000
39          2009-09-27 00:00:00.000
40          2009-10-04 00:00:00.000
41          2009-10-11 00:00:00.000
42          2009-10-18 00:00:00.000
43          2009-10-25 00:00:00.000
44          2009-11-01 00:00:00.000
45          2009-11-08 00:00:00.000
46          2009-11-15 00:00:00.000
47          2009-11-22 00:00:00.000
48          2009-11-29 00:00:00.000
49          2009-12-06 00:00:00.000
50          2009-12-13 00:00:00.000
51          2009-12-20 00:00:00.000
52          2009-12-27 00:00:00.000（所影响的行数为 52 行）
*/

Answer 9

create function fun_sunday(@year int)
returns @temp table(id int identity(1,1),sunday datetime)
as
begin
declare @t datetime,@endTime datetime
set @t=cast(@year as char(4))+'-01-01'
set @endTime=dateadd(year,1,@t)

while(@t<@endTime)
begin
if(datename(weekday,@t)='星期日')
begin
insert @temp(sunday)
  select @t

set @t=dateadd(day,7,@t)
continue
end
else
set @t=dateadd(day,1,@t)
end

return
endselect * from dbo.fun_sunday(2009)/*
id          sunday                                                 
----------- ------------------------------------------------------ 
1           2009-01-04 00:00:00.000
2           2009-01-11 00:00:00.000
3           2009-01-18 00:00:00.000
4           2009-01-25 00:00:00.000
5           2009-02-01 00:00:00.000
6           2009-02-08 00:00:00.000
7           2009-02-15 00:00:00.000
8           2009-02-22 00:00:00.000
9           2009-03-01 00:00:00.000
10          2009-03-08 00:00:00.000
11          2009-03-15 00:00:00.000
12          2009-03-22 00:00:00.000
13          2009-03-29 00:00:00.000
14          2009-04-05 00:00:00.000
15          2009-04-12 00:00:00.000
16          2009-04-19 00:00:00.000
17          2009-04-26 00:00:00.000
18          2009-05-03 00:00:00.000
19          2009-05-10 00:00:00.000
20          2009-05-17 00:00:00.000
21          2009-05-24 00:00:00.000
22          2009-05-31 00:00:00.000
23          2009-06-07 00:00:00.000
24          2009-06-14 00:00:00.000
25          2009-06-21 00:00:00.000
26          2009-06-28 00:00:00.000
27          2009-07-05 00:00:00.000
28          2009-07-12 00:00:00.000
29          2009-07-19 00:00:00.000
30          2009-07-26 00:00:00.000
31          2009-08-02 00:00:00.000
32          2009-08-09 00:00:00.000
33          2009-08-16 00:00:00.000
34          2009-08-23 00:00:00.000
35          2009-08-30 00:00:00.000
36          2009-09-06 00:00:00.000
37          2009-09-13 00:00:00.000
38          2009-09-20 00:00:00.000
39          2009-09-27 00:00:00.000
40          2009-10-04 00:00:00.000
41          2009-10-11 00:00:00.000
42          2009-10-18 00:00:00.000
43          2009-10-25 00:00:00.000
44          2009-11-01 00:00:00.000
45          2009-11-08 00:00:00.000
46          2009-11-15 00:00:00.000
47          2009-11-22 00:00:00.000
48          2009-11-29 00:00:00.000
49          2009-12-06 00:00:00.000
50          2009-12-13 00:00:00.000
51          2009-12-20 00:00:00.000
52          2009-12-27 00:00:00.000（所影响的行数为 52 行）
*/

Answer 10

SET DATEFIRST 1
DECLARE @date DATETIME
SET @date='2009-01-01'
while @date<='2009-12-31'
begin
    if DATEPART(dw,@date)=7
    begin
        print convert(varchar(10),@date,120)
    end
    set @date=@date+1
end