[mssql] Create Calendar Table

CREATE TABLE [Calendar]
(
    [CalendarDate] DATETIME
)
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = GETDATE()
SET @EndDate = DATEADD(d, 365, @StartDate)
WHILE @StartDate <= @EndDate
    BEGIN
        INSERT INTO [Calendar]
        (
           CalendarDate
        )
        SELECT
            @StartDate
        SET @StartDate = DATEADD(dd, 1, @StartDate)
    END

可將 @StartDate 或是 @EndDate 改成自己想要的範圍。

取最近七天的日期：

select
  CalendarDate
from
  Calendarwhere
  CalendarDate < GETDATE()and
  CalendarDate > DATEADD(d, -7, GETDATE())