检索并连接数周的历史日期

I have some old excel templates I am trying to replicate, the way they're set up aren't ideal but I using them as an exercise.

我有一些我试图复制的旧Excel模板，它们的设置方式并不理想，但我将它们用作练习。

In the excel tables there is a column that looks like this:

在excel表中有一个如下所示的列：

|--Week and dates--|
|------------------|
|Week 291214_040115|
|Week 050115_110115|
|Week 120115_180115|
|...etc............|
|...etc............|
|...etc............|
|...etc............|
|Week 030717_090717|
|Week 100717_160717|

With the code below I tried to recreate the above but I have run into several problems.

使用下面的代码我尝试重新创建上面的代码，但我遇到了几个问题。

Declare @sDate date,
        @eDate date;

Select  @sDate = '2015-01-01',
        @eDate = '2017-07-31';

;with cte as
(
select @sDate StartDate, 
DATEADD(wk, DATEDIFF(wk, 0, @sDate), 6) EndDate

union all

select dateadd(ww, 1, StartDate),
dateadd(ww, 1, EndDate)

from cte
where dateadd(ww, 1, StartDate)<=  @eDate
)

select concat(StartDate,'_',EndDate) as date

from cte
OPTION (MAXRECURSION 0)

The output this produces is:

它产生的输出是：

|------------Dates-------------|
|2015-01-01_Jan  4 2015 12:00AM|
|2015-01-08_Jan 11 2015 12:00AM|
|2015-01-15_Jan 18 2015 12:00AM|
|2015-01-22_Jan 25 2015 12:00AM|
|2015-01-29_Feb  1 2015 12:00AM|
|2015-02-05_Feb  8 2015 12:00AM|

I haven't concatenated the string "week" into the script yet, but basically how to I get the script to look more like the excel template table and less like my own version.

我还没有将字符串“week”连接到脚本中，但基本上我如何让脚本看起来更像excel模板表而不像我自己的版本。

1 个解决方案

#1

You can change your select as below:

您可以更改以下选项：

select concat('Week ',Format(StartDate, 'ddMMyy'),'_',Format(dateadd(day,6,StartDate),'ddMMyy')) as date
   from cte