如何使用SQL获取具有周id、开始日期和结束日期的BiWeekly日期？

Question

示例：Jan 2017

Week 1- 01/01/2017 - 01/14/2017

Week 2 - 01/15/2017 - 01/28/2017

等等..。

数据库: Server

Answer 1

试试这个：

WITH dates AS (
    SELECT DATEADD(DAY,number,CAST('2017-01-01' AS DATE)) day
    FROM master..spt_values WHERE TYPE = 'p'
    AND number < 366
    )

SELECT 
    DATEPART(WEEK, day) as Week, 
    day as FirstWeekDay,
    dateAdd(day, 6, day) as LastWeekDay
FROM dates
WHERE DATENAME(dw, day) IN ('Monday')
Order by day

或者像你的例子一样定期15天：

WITH dates AS (
    SELECT DATEADD(DAY,number,CAST('2017-01-01' AS DATE)) day
    FROM master..spt_values WHERE TYPE = 'p'
    AND number < 366
    )

SELECT 
    row_number() over (order by day),
    day as FirstWeekDay,
    dateAdd(day, 13, day) as LastWeekDay
FROM dates
WHERE DATENAME(dw, day) IN ('Sunday')
 and DATEPART(WEEK, day) % 2 = 1
order by day

更新

如果您的Calendar表有所有的时间，则可以使用此查询：

SELECT 
    row_number() over (order by day_date) WeekId,
    day_date as FirstWeekDay,
    dateAdd(day, 6, day_date) as LastWeekDay  --change 6 -> 13 for 2 weeks
FROM Calendar
WHERE DATENAME(dw, day_date) IN ('Monday')    --change day name if you need
-- and DATEPART(WEEK, day_date) % 2 = 1       --remove comment for 2 weeks
order by day_date