高分求:建立一个存储过程计算出一年有几周 建立一个存储过程计算出一年有几周,每一周是几年几月几号到几年几月几号数据库是SQL2005弄了很久 弄不出啊 没头绪 求指教 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 select datepart(weekday,dateadd(dd,number,'2011-01-01'))-1 as 星期,dateadd(dd,number,'2011-01-01') as 日期from master..spt_valueswhere type='p' and number < datediff(dd,'2011-01-01','2012-01-01') 做一个日历表http://topic.csdn.net/u/20100528/16/f3c160a2-6d97-4e19-8f74-154d34a940d7.html?41962 with cte as( select dt=dateadd(d,number,'2011-01-01') from master..spt_values where type = 'P' and dateadd(d,number,'2011-01-01') <= '2011-12-31')select 周 = datepart(wk,dt), 开始日期 = min(dt), 结束日期 = max(dt) from cte group by datepart(wk,dt)/*周 开始日期 结束日期----------- ----------------------- -----------------------1 2011-01-01 00:00:00.000 2011-01-01 00:00:00.0002 2011-01-02 00:00:00.000 2011-01-08 00:00:00.0003 2011-01-09 00:00:00.000 2011-01-15 00:00:00.0004 2011-01-16 00:00:00.000 2011-01-22 00:00:00.0005 2011-01-23 00:00:00.000 2011-01-29 00:00:00.0006 2011-01-30 00:00:00.000 2011-02-05 00:00:00.0007 2011-02-06 00:00:00.000 2011-02-12 00:00:00.0008 2011-02-13 00:00:00.000 2011-02-19 00:00:00.0009 2011-02-20 00:00:00.000 2011-02-26 00:00:00.00010 2011-02-27 00:00:00.000 2011-03-05 00:00:00.00011 2011-03-06 00:00:00.000 2011-03-12 00:00:00.00012 2011-03-13 00:00:00.000 2011-03-19 00:00:00.00013 2011-03-20 00:00:00.000 2011-03-26 00:00:00.00014 2011-03-27 00:00:00.000 2011-04-02 00:00:00.00015 2011-04-03 00:00:00.000 2011-04-09 00:00:00.00016 2011-04-10 00:00:00.000 2011-04-16 00:00:00.00017 2011-04-17 00:00:00.000 2011-04-23 00:00:00.00018 2011-04-24 00:00:00.000 2011-04-30 00:00:00.00019 2011-05-01 00:00:00.000 2011-05-07 00:00:00.00020 2011-05-08 00:00:00.000 2011-05-14 00:00:00.00021 2011-05-15 00:00:00.000 2011-05-21 00:00:00.00022 2011-05-22 00:00:00.000 2011-05-28 00:00:00.00023 2011-05-29 00:00:00.000 2011-06-04 00:00:00.00024 2011-06-05 00:00:00.000 2011-06-11 00:00:00.00025 2011-06-12 00:00:00.000 2011-06-18 00:00:00.00026 2011-06-19 00:00:00.000 2011-06-25 00:00:00.00027 2011-06-26 00:00:00.000 2011-07-02 00:00:00.00028 2011-07-03 00:00:00.000 2011-07-09 00:00:00.00029 2011-07-10 00:00:00.000 2011-07-16 00:00:00.00030 2011-07-17 00:00:00.000 2011-07-23 00:00:00.00031 2011-07-24 00:00:00.000 2011-07-30 00:00:00.00032 2011-07-31 00:00:00.000 2011-08-06 00:00:00.00033 2011-08-07 00:00:00.000 2011-08-13 00:00:00.00034 2011-08-14 00:00:00.000 2011-08-20 00:00:00.00035 2011-08-21 00:00:00.000 2011-08-27 00:00:00.00036 2011-08-28 00:00:00.000 2011-09-03 00:00:00.00037 2011-09-04 00:00:00.000 2011-09-10 00:00:00.00038 2011-09-11 00:00:00.000 2011-09-17 00:00:00.00039 2011-09-18 00:00:00.000 2011-09-24 00:00:00.00040 2011-09-25 00:00:00.000 2011-10-01 00:00:00.00041 2011-10-02 00:00:00.000 2011-10-08 00:00:00.00042 2011-10-09 00:00:00.000 2011-10-15 00:00:00.00043 2011-10-16 00:00:00.000 2011-10-22 00:00:00.00044 2011-10-23 00:00:00.000 2011-10-29 00:00:00.00045 2011-10-30 00:00:00.000 2011-11-05 00:00:00.00046 2011-11-06 00:00:00.000 2011-11-12 00:00:00.00047 2011-11-13 00:00:00.000 2011-11-19 00:00:00.00048 2011-11-20 00:00:00.000 2011-11-26 00:00:00.00049 2011-11-27 00:00:00.000 2011-12-03 00:00:00.00050 2011-12-04 00:00:00.000 2011-12-10 00:00:00.00051 2011-12-11 00:00:00.000 2011-12-17 00:00:00.00052 2011-12-18 00:00:00.000 2011-12-24 00:00:00.00053 2011-12-25 00:00:00.000 2011-12-31 00:00:00.000(53 行受影响)*/ select substring(convert(varchar,dateadd(day,x,col),120),1,10) as 开始日期,substring(convert(varchar,dateadd(day,x,col+6),120),1,10) as 结束日期 from ( select cast('2011-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))=2/*开始日期 结束日期-------------------- --------------------2011-01-03 2011-01-092011-01-10 2011-01-162011-01-17 2011-01-232011-01-24 2011-01-302011-01-31 2011-02-062011-02-07 2011-02-132011-02-14 2011-02-202011-02-21 2011-02-272011-02-28 2011-03-062011-03-07 2011-03-132011-03-14 2011-03-202011-03-21 2011-03-272011-03-28 2011-04-032011-04-04 2011-04-102011-04-11 2011-04-172011-04-18 2011-04-242011-04-25 2011-05-012011-05-02 2011-05-082011-05-09 2011-05-152011-05-16 2011-05-222011-05-23 2011-05-292011-05-30 2011-06-052011-06-06 2011-06-122011-06-13 2011-06-192011-06-20 2011-06-262011-06-27 2011-07-032011-07-04 2011-07-102011-07-11 2011-07-172011-07-18 2011-07-242011-07-25 2011-07-312011-08-01 2011-08-072011-08-08 2011-08-142011-08-15 2011-08-212011-08-22 2011-08-282011-08-29 2011-09-042011-09-05 2011-09-112011-09-12 2011-09-182011-09-19 2011-09-252011-09-26 2011-10-022011-10-03 2011-10-092011-10-10 2011-10-162011-10-17 2011-10-232011-10-24 2011-10-302011-10-31 2011-11-062011-11-07 2011-11-132011-11-14 2011-11-202011-11-21 2011-11-272011-11-28 2011-12-042011-12-05 2011-12-112011-12-12 2011-12-182011-12-19 2011-12-252011-12-26 2012-01-01*/ 不同存储过程中的本地临时表名一样 成功插入后显示"1行被上次查询影响" [玻璃鱼V]问些数据库建表基础问题 如何清除表中的null? 没分了!30求解~~查询条件为in 一个字符串的问题,字符串为所要查询结果的主键,用逗号隔开 问一个用关系代数完成查询的问题 深夜求助,还有人在吗? 在不同的服务器之间调用函数 如何将两张表合并成一张表!!!! 超大数据库删除数据问题 这种取值应该如何写 数据库访问超时?请教高手。。。
from master..spt_values
where type='p' and number < datediff(dd,'2011-01-01','2012-01-01')
http://topic.csdn.net/u/20100528/16/f3c160a2-6d97-4e19-8f74-154d34a940d7.html?41962
with cte as
(
select dt=dateadd(d,number,'2011-01-01')
from master..spt_values
where type = 'P' and dateadd(d,number,'2011-01-01') <= '2011-12-31'
)
select 周 = datepart(wk,dt), 开始日期 = min(dt), 结束日期 = max(dt) from cte group by datepart(wk,dt)/*
周 开始日期 结束日期
----------- ----------------------- -----------------------
1 2011-01-01 00:00:00.000 2011-01-01 00:00:00.000
2 2011-01-02 00:00:00.000 2011-01-08 00:00:00.000
3 2011-01-09 00:00:00.000 2011-01-15 00:00:00.000
4 2011-01-16 00:00:00.000 2011-01-22 00:00:00.000
5 2011-01-23 00:00:00.000 2011-01-29 00:00:00.000
6 2011-01-30 00:00:00.000 2011-02-05 00:00:00.000
7 2011-02-06 00:00:00.000 2011-02-12 00:00:00.000
8 2011-02-13 00:00:00.000 2011-02-19 00:00:00.000
9 2011-02-20 00:00:00.000 2011-02-26 00:00:00.000
10 2011-02-27 00:00:00.000 2011-03-05 00:00:00.000
11 2011-03-06 00:00:00.000 2011-03-12 00:00:00.000
12 2011-03-13 00:00:00.000 2011-03-19 00:00:00.000
13 2011-03-20 00:00:00.000 2011-03-26 00:00:00.000
14 2011-03-27 00:00:00.000 2011-04-02 00:00:00.000
15 2011-04-03 00:00:00.000 2011-04-09 00:00:00.000
16 2011-04-10 00:00:00.000 2011-04-16 00:00:00.000
17 2011-04-17 00:00:00.000 2011-04-23 00:00:00.000
18 2011-04-24 00:00:00.000 2011-04-30 00:00:00.000
19 2011-05-01 00:00:00.000 2011-05-07 00:00:00.000
20 2011-05-08 00:00:00.000 2011-05-14 00:00:00.000
21 2011-05-15 00:00:00.000 2011-05-21 00:00:00.000
22 2011-05-22 00:00:00.000 2011-05-28 00:00:00.000
23 2011-05-29 00:00:00.000 2011-06-04 00:00:00.000
24 2011-06-05 00:00:00.000 2011-06-11 00:00:00.000
25 2011-06-12 00:00:00.000 2011-06-18 00:00:00.000
26 2011-06-19 00:00:00.000 2011-06-25 00:00:00.000
27 2011-06-26 00:00:00.000 2011-07-02 00:00:00.000
28 2011-07-03 00:00:00.000 2011-07-09 00:00:00.000
29 2011-07-10 00:00:00.000 2011-07-16 00:00:00.000
30 2011-07-17 00:00:00.000 2011-07-23 00:00:00.000
31 2011-07-24 00:00:00.000 2011-07-30 00:00:00.000
32 2011-07-31 00:00:00.000 2011-08-06 00:00:00.000
33 2011-08-07 00:00:00.000 2011-08-13 00:00:00.000
34 2011-08-14 00:00:00.000 2011-08-20 00:00:00.000
35 2011-08-21 00:00:00.000 2011-08-27 00:00:00.000
36 2011-08-28 00:00:00.000 2011-09-03 00:00:00.000
37 2011-09-04 00:00:00.000 2011-09-10 00:00:00.000
38 2011-09-11 00:00:00.000 2011-09-17 00:00:00.000
39 2011-09-18 00:00:00.000 2011-09-24 00:00:00.000
40 2011-09-25 00:00:00.000 2011-10-01 00:00:00.000
41 2011-10-02 00:00:00.000 2011-10-08 00:00:00.000
42 2011-10-09 00:00:00.000 2011-10-15 00:00:00.000
43 2011-10-16 00:00:00.000 2011-10-22 00:00:00.000
44 2011-10-23 00:00:00.000 2011-10-29 00:00:00.000
45 2011-10-30 00:00:00.000 2011-11-05 00:00:00.000
46 2011-11-06 00:00:00.000 2011-11-12 00:00:00.000
47 2011-11-13 00:00:00.000 2011-11-19 00:00:00.000
48 2011-11-20 00:00:00.000 2011-11-26 00:00:00.000
49 2011-11-27 00:00:00.000 2011-12-03 00:00:00.000
50 2011-12-04 00:00:00.000 2011-12-10 00:00:00.000
51 2011-12-11 00:00:00.000 2011-12-17 00:00:00.000
52 2011-12-18 00:00:00.000 2011-12-24 00:00:00.000
53 2011-12-25 00:00:00.000 2011-12-31 00:00:00.000(53 行受影响)
*/
select substring(convert(varchar,dateadd(day,x,col),120),1,10) as 开始日期,
substring(convert(varchar,dateadd(day,x,col+6),120),1,10) as 结束日期 from
(
select cast('2011-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))=2
/*
开始日期 结束日期
-------------------- --------------------
2011-01-03 2011-01-09
2011-01-10 2011-01-16
2011-01-17 2011-01-23
2011-01-24 2011-01-30
2011-01-31 2011-02-06
2011-02-07 2011-02-13
2011-02-14 2011-02-20
2011-02-21 2011-02-27
2011-02-28 2011-03-06
2011-03-07 2011-03-13
2011-03-14 2011-03-20
2011-03-21 2011-03-27
2011-03-28 2011-04-03
2011-04-04 2011-04-10
2011-04-11 2011-04-17
2011-04-18 2011-04-24
2011-04-25 2011-05-01
2011-05-02 2011-05-08
2011-05-09 2011-05-15
2011-05-16 2011-05-22
2011-05-23 2011-05-29
2011-05-30 2011-06-05
2011-06-06 2011-06-12
2011-06-13 2011-06-19
2011-06-20 2011-06-26
2011-06-27 2011-07-03
2011-07-04 2011-07-10
2011-07-11 2011-07-17
2011-07-18 2011-07-24
2011-07-25 2011-07-31
2011-08-01 2011-08-07
2011-08-08 2011-08-14
2011-08-15 2011-08-21
2011-08-22 2011-08-28
2011-08-29 2011-09-04
2011-09-05 2011-09-11
2011-09-12 2011-09-18
2011-09-19 2011-09-25
2011-09-26 2011-10-02
2011-10-03 2011-10-09
2011-10-10 2011-10-16
2011-10-17 2011-10-23
2011-10-24 2011-10-30
2011-10-31 2011-11-06
2011-11-07 2011-11-13
2011-11-14 2011-11-20
2011-11-21 2011-11-27
2011-11-28 2011-12-04
2011-12-05 2011-12-11
2011-12-12 2011-12-18
2011-12-19 2011-12-25
2011-12-26 2012-01-01
*/