請幫取出下一周的記錄，並按年月，天分開

請幫取出上一周的記錄，並按年月，天分開

舉例：今天2012-07-18號，則上周的記錄為
YM D
2012079
20120710
20120711
20120712
20120713
20120714
20120715

說明：請不要用while循環及直接一倏倏插入,謝謝！

[解决办法]

SQL code

declare @date datetimeset @date = '2012-07-18'select distinct dateadd(dd,-1*number,@date)from master..spt_valueswhere datepart(week,dateadd(dd,-1*number,@date)) = datepart(week,@date) - 1    and [type] = 'P' and number between 1 and 15/***********************-----------------------2012-07-08 00:00:00.0002012-07-09 00:00:00.0002012-07-10 00:00:00.0002012-07-11 00:00:00.0002012-07-12 00:00:00.0002012-07-13 00:00:00.0002012-07-14 00:00:00.000(7 行受影响)
[解决办法]
SQL code
declare @date datetimeset @date = '2012-07-18'set datefirst 1select  convert(varchar(8),dateadd(dd,-1*number,@date),112) as YM,datepart(dd,dateadd(dd,-1*number,@date)) as Dfrom master..spt_valueswhere datepart(week,dateadd(dd,-1*number,@date)) = datepart(week,@date) - 1and [type] = 'P' and number between 1 and 15/*YM       D-------- -----------20120715 1520120714 1420120713 1320120712 1220120711 1120120710 1020120709 9(7 行受影响)*/