日期:2014-05-17 浏览次数:20739 次
select
DATEADD(dd,number,'2012-10-10') dt,
datepart(WK,DATEADD(dd,number,'2012-10-10')) as wk
from
master..spt_values
where
number between 0 and DATEDIFF(dd,'2012-10-10','2012-11-30')
and type='p'
/*
dt wk
----------------------- -----------
2012-10-10 00:00:00.000 41
2012-10-11 00:00:00.000 41
2012-10-12 00:00:00.000 41
2012-10-13 00:00:00.000 41
2012-10-14 00:00:00.000 42
2012-10-15 00:00:00.000 42
2012-10-16 00:00:00.000 42
2012-10-17 00:00:00.000 42
2012-10-18 00:00:00.000 42
2012-10-19 00:00:00.000 42
2012-10-20 00:00:00.000 42
2012-10-21 00:00:00.000 43
2012-10-22 00:00:00.000 43
2012-10-23 00:00:00.000 43
2012-10-24 00:00:00.000 43
2012-10-25 00:00:00.000 43
2012-10-26 00:00:00.000 43
2012-10-27 00:00:00.000 43
2012-10-28 00:00:00.000 44
2012-10-29 00:00:00.000 44
2012-10-30 00:00:00.000 44
2012-10-31 00:00:00.000 44
2012-11-01 00:00:00.000 44
2012-11-02 00:00:00.000 44
2012-11-03 00:00:00.000 44
2012-11-04 00:00:00.000 45
2012-11-05 00:00:00.000 45
2012-11-06 00:00:00.000 45
2012-11-07 00:00:00.000 45
2012-11-08 00:00:00.000 45
2012-11-09 00:00:00.000 45
2012-11-10 00:00:00.000 45
2012-11-11 00:00:00.000 46
2012-11-12 00:00:00.000 46
2012-11-13 00:00:00.000 46
2012-11-14 00:00:00.000 46
2012-11-15 00:00:00.000 46
2012-11-16 00:00:00.000 46
2012-11-17 00:00:00.000 46
2012-11-18 00:00:00.000 47
2012-11-19 00:00:00.000 47
2012-11-20 00:00:00.000 47
2012-11-21 00:00:00.000 47
2012-11-22 00:00:00.000 47
2012-11-23 00:00:00.000 47
2012-11-24 00:00:00.000 47
2012-11-25 00:00:00.000 48
2012-11-26 00:00:00.000 48
2012-11-27 00:00:00.000 48
2012-11-28 00:00:00.000 48
2012-11-29 00:00:00.000 48
2012-11-30 00:00:00.000 48
(52 行受影响)
*/
USE test
GO
---->生成表tb
--if object_id('tb') is not null
-- drop table tb
--Go
---- test data
--Create table tb([日期] datetime,[OrderNr] nvarchar(3),[CustomerName] nvarchar(1))
--Insert into tb
--Select '2012-01-01',N'#1',N'A'
--Union all Select '2012-01-02',N'#2',N'A'
--Union all Select '2012-01-03',N'#3',N'A'
--Union all Select '2012-01-04',N'#4',N'A'
--Union all Select '2012-02-01',N'#5',N'A'
--Union all Select '2012-02-02',N'#6',N'B'
--Union all Select '2012-02-03',N'#7',N'B'
--Union all Select '2012-05-14',N'#8',N'B'
--Union all Select '2012-05-16',N'#9',N'C'
--Union&nb