生成日期范围之间的日期

温柔戏命师 2024-12-17 13:19:41

轻松使用 SQL 2005+；如果您有数字或统计表，则更容易。我在下面伪造了它：

DECLARE @StartDate DATE = '20110901'
  , @EndDate DATE = '20111001'

SELECT  DATEADD(DAY, nbr - 1, @StartDate)
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY c.object_id ) AS nbr
          FROM      sys.columns c
        ) nbrs
WHERE   nbr - 1 <= DATEDIFF(DAY, @StartDate, @EndDate)

如果您有一个统计表，请用该表替换子查询。没有递归。

编辑：由于人们似乎对统计表有疑问，让我使用从零开始的统计表重写它。首先，这是一些用于创建和填充表的代码。

CREATE TABLE [dbo].[nbrs](
    [nbr] [INT] NOT NULL
) ON [PRIMARY]
GO


CREATE UNIQUE CLUSTERED INDEX [clidx] ON [dbo].[nbrs]
(
    [nbr] ASC
)
GO

INSERT INTO dbo.nbrs (nbr)
SELECT nbr-1
FROM ( SELECT    ROW_NUMBER() OVER ( ORDER BY c.object_id ) AS nbr
          FROM      sys.columns c
        ) nbrs
GO

现在，您已将数字表作为数据库中的永久对象，您可以将其重新用于查询而不是子查询。该查询也已被编辑为使用从零开始的计算。

DECLARE @StartDate DATE = '20110901'
      , @EndDate DATE = '20111001'

SELECT  DATEADD(DAY, nbr, @DateStart)
FROM    nbrs
WHERE   nbr <= DATEDIFF(DAY, @DateStart, @DateEnd)

高性能，并且没有递归。

Easy on SQL 2005+; easier if you have a numbers or tally table. I faked it below:

DECLARE @StartDate DATE = '20110901'
  , @EndDate DATE = '20111001'

SELECT  DATEADD(DAY, nbr - 1, @StartDate)
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY c.object_id ) AS nbr
          FROM      sys.columns c
        ) nbrs
WHERE   nbr - 1 <= DATEDIFF(DAY, @StartDate, @EndDate)

If you have a tally table, replace the subquery with the table. No recursion.

EDIT: Since folks seem to have questions about the tally table, let me rewrite this using a zero-based tally table. First, here's some code to create and populate a table.

CREATE TABLE [dbo].[nbrs](
    [nbr] [INT] NOT NULL
) ON [PRIMARY]
GO


CREATE UNIQUE CLUSTERED INDEX [clidx] ON [dbo].[nbrs]
(
    [nbr] ASC
)
GO

INSERT INTO dbo.nbrs (nbr)
SELECT nbr-1
FROM ( SELECT    ROW_NUMBER() OVER ( ORDER BY c.object_id ) AS nbr
          FROM      sys.columns c
        ) nbrs
GO

Now, that you have the numbers table as a permanent object in your database, you can reuse it for the query INSTEAD of the subquery. The query has also been edited to use a zero-based calculation.

DECLARE @StartDate DATE = '20110901'
      , @EndDate DATE = '20111001'

SELECT  DATEADD(DAY, nbr, @DateStart)
FROM    nbrs
WHERE   nbr <= DATEDIFF(DAY, @DateStart, @DateEnd)

Performant, and no recursion.

回复收藏 0 原文

寄意 2024-12-17 13:19:41

如果您使用的是 SQL Server 2005 或更高版本，请尝试此操作：这是

WITH Dates AS (
        SELECT
         [Date] = CONVERT(DATETIME,'09/01/2011')
        UNION ALL SELECT
         [Date] = DATEADD(DAY, 1, [Date])
        FROM
         Dates
        WHERE
         Date < '10/10/2011'
) SELECT
 [Date]
FROM
 Dates
 OPTION (MAXRECURSION 45)

一个可以使用 CTE 完成的很酷的事情的一个很好的示例。

Try this if you are using SQL Server 2005 or newer:

WITH Dates AS (
        SELECT
         [Date] = CONVERT(DATETIME,'09/01/2011')
        UNION ALL SELECT
         [Date] = DATEADD(DAY, 1, [Date])
        FROM
         Dates
        WHERE
         Date < '10/10/2011'
) SELECT
 [Date]
FROM
 Dates
 OPTION (MAXRECURSION 45)

A good example of cool stuff you can do with a CTE.

回复收藏 0 原文

电影里的梦 2024-12-17 13:19:41

-- 声明

DECLARE @dates TABLE(dt DATE)    
DECLARE @dateFrom DATE
DECLARE @dateTo DATE

SET @dateFrom = '2001/01/01'
SET @dateTo = '2001/01/12'

-- 查询：

WHILE(@dateFrom <= @dateTo)
BEGIN
   INSERT INTO @dates 
   SELECT @dateFrom

   SELECT @dateFrom = DATEADD(day, 1, @dateFrom)
END

-- 输出

SELECT * FROM @dates

-- Declarations

DECLARE @dates TABLE(dt DATE)    
DECLARE @dateFrom DATE
DECLARE @dateTo DATE

SET @dateFrom = '2001/01/01'
SET @dateTo = '2001/01/12'

-- Query:

WHILE(@dateFrom <= @dateTo)
BEGIN
   INSERT INTO @dates 
   SELECT @dateFrom

   SELECT @dateFrom = DATEADD(day, 1, @dateFrom)
END

-- Output

SELECT * FROM @dates

回复收藏 0 原文

悲念泪 2024-12-17 13:19:41

这是一个不需要递归的解决方案，同时，这个表值函数可以在许多查询中重复使用，而不需要再次重复声明样板变量。对于那些不想递归的人来说，这是唯一的选择。

创建这个简单的函数：

CREATE FUNCTION [dbo].[GenerateDateRange]
(@StartDate AS DATE,
 @EndDate AS   DATE,
 @Interval AS  INT
)
RETURNS @Dates TABLE(DateValue DATE)
AS
BEGIN
    DECLARE @CUR_DATE DATE
    SET @CUR_DATE = @StartDate
    WHILE @CUR_DATE <= @EndDate BEGIN
        INSERT INTO @Dates VALUES(@CUR_DATE)
        SET @CUR_DATE = DATEADD(DAY, @Interval, @CUR_DATE)
    END
    RETURN;
END;

然后选择依据：

select *
from dbo.GenerateDateRange('2017-01-03', '2017-12-01', 1)

Here is a solution that does not require recursion, and at the same time, this table-valued function is re-usable in many queries without the need to repeat the declaration of boilerplate variables again. This is the only alternative, for those who don't want recursion.

Create this simple function:

CREATE FUNCTION [dbo].[GenerateDateRange]
(@StartDate AS DATE,
 @EndDate AS   DATE,
 @Interval AS  INT
)
RETURNS @Dates TABLE(DateValue DATE)
AS
BEGIN
    DECLARE @CUR_DATE DATE
    SET @CUR_DATE = @StartDate
    WHILE @CUR_DATE <= @EndDate BEGIN
        INSERT INTO @Dates VALUES(@CUR_DATE)
        SET @CUR_DATE = DATEADD(DAY, @Interval, @CUR_DATE)
    END
    RETURN;
END;

And then select by:

select *
from dbo.GenerateDateRange('2017-01-03', '2017-12-01', 1)

回复收藏 0 原文

花桑 2024-12-17 13:19:41

我意识到这是一个旧线程，但我不得不承认我对这里给出的过多的递归和循环解决方案感到沮丧。我想知道有多少人意识到递归只不过是一个非常昂贵的循环？我理解创建表值函数的愿望，但我建议以下方法更加有效，因为它是基于集合的，无需循环、递归或重复的单个插入语句：

CREATE FUNCTION dbo.GenerateDateRange(@StartDate AS DATE, @EndDate AS DATE)
RETURNS TABLE WITH SCHEMABINDING AS
    WITH e1(n) AS (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS x(n)) -- 16 records
        ,e2(n) AS (SELECT 1 FROM e1 a CROSS JOIN e1 b) -- 16^2 or 256 records (16*16)
        ,cteTally(n) AS (SELECT ROW_NUMBER() over (ORDER BY 1) AS n FROM e2 a CROSS JOIN e2 b) -- 16^4 or 65,536 records (256*256)
    SELECT DATEADD(DAY, n-1, @StartDate)
    FROM cteTally
    WHERE n <= DATEDIFF(DAY, @StartDate, @EndDate) + 1;
GO

I realize that this is an old thread, but I have to admit my dismay at the overabundance of recursive and looping solutions given here. I wonder just how many folks realize that recursion is nothing more than a very expensive loop? I understand the desire to create a Table-Valued Function, but I suggest that the following is far more efficient as it is set-based, without looping, recursion, or repeated single insert statements:

CREATE FUNCTION dbo.GenerateDateRange(@StartDate AS DATE, @EndDate AS DATE)
RETURNS TABLE WITH SCHEMABINDING AS
    WITH e1(n) AS (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS x(n)) -- 16 records
        ,e2(n) AS (SELECT 1 FROM e1 a CROSS JOIN e1 b) -- 16^2 or 256 records (16*16)
        ,cteTally(n) AS (SELECT ROW_NUMBER() over (ORDER BY 1) AS n FROM e2 a CROSS JOIN e2 b) -- 16^4 or 65,536 records (256*256)
    SELECT DATEADD(DAY, n-1, @StartDate)
    FROM cteTally
    WHERE n <= DATEDIFF(DAY, @StartDate, @EndDate) + 1;
GO

回复收藏 0 原文

怀中猫帐中妖 2024-12-17 13:19:41

这是一个旧线程，但如果它对任何人有帮助，这就是我在支持 CTE 的 SQL Server 现代版本中使用的内容。这还为您提供了星期几，并且可以对其进行调整以提供您可能需要的其他值（即季度、月份等）。

DECLARE @StartDate datetime
DECLARE @EndDate datetime
SET @StartDate = '1/1/2020'
SET @EndDate = '12/31/2020'
DECLARE @DayTable Table(theDate date, theDayOfWeek nvarchar(50));
WITH DayTable AS (SELECT CAST(@StartDate AS DATETIME) theDate, DATENAME(dw, @StartDate) theDayOfWeek UNION ALL SELECT DATEADD(dd, 1, theDate), DATENAME(dw,DATEADD(dd, 1, theDate)) FROM DayTable s  WHERE DATEADD(dd, 1, theDate) <= CAST(@EndDate AS DATETIME)) 
INSERT INTO @DayTable(theDate, theDayOfWeek) SELECT theDate, theDayOfWeek FROM DayTable OPTION (MAXRECURSION 365); 
SELECT * FROM @DayTable

This is an old thread, but in case it helps anyone, this is what I use in modern versions of SQL Server that support CTE's. This also gives you the Day of the Week and it can be tweaked to give other values you may need (i.e. Quarter, Month, etc.).

DECLARE @StartDate datetime
DECLARE @EndDate datetime
SET @StartDate = '1/1/2020'
SET @EndDate = '12/31/2020'
DECLARE @DayTable Table(theDate date, theDayOfWeek nvarchar(50));
WITH DayTable AS (SELECT CAST(@StartDate AS DATETIME) theDate, DATENAME(dw, @StartDate) theDayOfWeek UNION ALL SELECT DATEADD(dd, 1, theDate), DATENAME(dw,DATEADD(dd, 1, theDate)) FROM DayTable s  WHERE DATEADD(dd, 1, theDate) <= CAST(@EndDate AS DATETIME)) 
INSERT INTO @DayTable(theDate, theDayOfWeek) SELECT theDate, theDayOfWeek FROM DayTable OPTION (MAXRECURSION 365); 
SELECT * FROM @DayTable

回复收藏 0 原文

怂人 2024-12-17 13:19:41

使用MVJ的F_TABLE_DATE函数，简直太棒了：

http://www.sqlteam.com /forums/topic.asp?TOPIC_ID=61519

一旦您实现此功能，只需传入开始和结束日期，您就可以插入其间的所有日期。

回复收藏 0 原文

夏末染殇 2024-12-17 13:19:41

如果由于某种原因您无法声明变量，例如使用Looker 中的 ="nofollow noreferrer">派生表，你可以这样：

select
  dateadd(day, nbr - 1, convert(date, '2017-01-01')) as d
from (
  select row_number() over (order by c.object_id) as nbr from sys.columns c
) nbrs
where
  nbr - 1 <= datediff(
    day,
    convert(date, '2017-01-01'),
    convert(date, '2018-12-31')
  )

顺便说一下，这个你的约会方式是这样的系列视图在 LookerML 中可能类似于：

view: date_series {
  derived_table: {
    sql:
      select
        dateadd(day, nbr - 1, convert(date, '2017-01-01')) as d
      from (
        select row_number() over (order by c.object_id) as nbr from sys.columns c
      ) nbrs
      where
        nbr - 1 <= datediff(day, convert(date, '2017-01-01'), convert(date, '2018-12-31')) ;;
  }

  dimension: date {
    primary_key: yes
    type: date
    sql: ${TABLE}.d ;;
  }
}

If for some reason you can't declare variables, such as when using derived tables in Looker, you can go like this:

select
  dateadd(day, nbr - 1, convert(date, '2017-01-01')) as d
from (
  select row_number() over (order by c.object_id) as nbr from sys.columns c
) nbrs
where
  nbr - 1 <= datediff(
    day,
    convert(date, '2017-01-01'),
    convert(date, '2018-12-31')
  )

By the way, this is how your date series view could look like in LookerML:

view: date_series {
  derived_table: {
    sql:
      select
        dateadd(day, nbr - 1, convert(date, '2017-01-01')) as d
      from (
        select row_number() over (order by c.object_id) as nbr from sys.columns c
      ) nbrs
      where
        nbr - 1 <= datediff(day, convert(date, '2017-01-01'), convert(date, '2018-12-31')) ;;
  }

  dimension: date {
    primary_key: yes
    type: date
    sql: ${TABLE}.d ;;
  }
}

回复收藏 0 原文

夏の忆 2024-12-17 13:19:41

尝试以下代码：

DECLARE @DateStart DATE = '2021-01-20' , @DateEnd DATE = '2021-01-29';
with Extract_Dates_CTE (MyDate) as (
    select @DateStart
    Union ALL
    select DATEADD(day, 1, MyDate)
    from Extract_Dates_CTE
    where MyDate < @DateEnd
)
select ROW_NUMBER() OVER(ORDER BY a.MyDate) AS RowDateID, a.MyDate AS ExtractedDates
from Extract_Dates_CTE a;

检查性能，我发现使用CTE方法具有更好的性能，如图所示。为此，我使用了两个查询并使用 SQL Server 工具显示了性能。

DECLARE @DateStart DATE = '2021-01-20' , @DateEnd DATE = '2021-01-29';
with Extract_Dates_CTE (MyDate) as (
    select @DateStart
    Union ALL
    select DATEADD(day, 1, MyDate)
    from Extract_Dates_CTE
    where MyDate < @DateEnd
)
select ROW_NUMBER() OVER(ORDER BY a.MyDate) AS RowDateID, a.MyDate AS ExtractedDates
from Extract_Dates_CTE a;

SELECT  DATEADD(DAY, nbr - 1, @DateStart)
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY c.object_id ) AS nbr
          FROM      sys.columns c
        ) nbrs
WHERE   nbr - 1 <= DATEDIFF(DAY, @DateStart, @DateEnd)

Try Following CODE:

DECLARE @DateStart DATE = '2021-01-20' , @DateEnd DATE = '2021-01-29';
with Extract_Dates_CTE (MyDate) as (
    select @DateStart
    Union ALL
    select DATEADD(day, 1, MyDate)
    from Extract_Dates_CTE
    where MyDate < @DateEnd
)
select ROW_NUMBER() OVER(ORDER BY a.MyDate) AS RowDateID, a.MyDate AS ExtractedDates
from Extract_Dates_CTE a;

Examining the performance, I found that using the CTE method has a better performance that I have shown in the figure. For this purpose, I used two queries and displayed the performance using the SQL Server tool.

DECLARE @DateStart DATE = '2021-01-20' , @DateEnd DATE = '2021-01-29';
with Extract_Dates_CTE (MyDate) as (
    select @DateStart
    Union ALL
    select DATEADD(day, 1, MyDate)
    from Extract_Dates_CTE
    where MyDate < @DateEnd
)
select ROW_NUMBER() OVER(ORDER BY a.MyDate) AS RowDateID, a.MyDate AS ExtractedDates
from Extract_Dates_CTE a;

SELECT  DATEADD(DAY, nbr - 1, @DateStart)
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY c.object_id ) AS nbr
          FROM      sys.columns c
        ) nbrs
WHERE   nbr - 1 <= DATEDIFF(DAY, @DateStart, @DateEnd)

回复收藏 0 原文

吖咩 2024-12-17 13:19:41

使用 @Abe Miesler 的答案，为了其他人的方便，我将其构建到 SQL Server 2008 及以后的 TVF 中。它可能对其他人有帮助 - 我必须找到一种方法将 CTE 包含在 TVF 中！

    --Generate a range of dates with interval option, courtesy of Abe Miessler for the core query here!
CREATE OR ALTER FUNCTION [dbo].[DateRange]
(@startDate AS DATE,
 @EndDate AS   DATE,
 @interval AS  INT
)
RETURNS @Dates TABLE(dateValue DATE)
AS
     BEGIN
         WITH Dates
              AS (
              SELECT [Date] = CONVERT( DATETIME, @startDate)
              UNION ALL
              SELECT [Date] = DATEADD(DAY, ISNULL(@interval, 1), [Date])
              FROM Dates
              WHERE Date < @EndDate)
              INSERT INTO @Dates
                     SELECT [Date]
                     FROM Dates
                     OPTION(MAXRECURSION 900);
         RETURN;
     END;

Using @Abe Miesler's answer, for other's convenience I built it into a TVF for SQL Server 2008 onwards. It may help others - I had to find a way to include the CTE inside the TVF!

    --Generate a range of dates with interval option, courtesy of Abe Miessler for the core query here!
CREATE OR ALTER FUNCTION [dbo].[DateRange]
(@startDate AS DATE,
 @EndDate AS   DATE,
 @interval AS  INT
)
RETURNS @Dates TABLE(dateValue DATE)
AS
     BEGIN
         WITH Dates
              AS (
              SELECT [Date] = CONVERT( DATETIME, @startDate)
              UNION ALL
              SELECT [Date] = DATEADD(DAY, ISNULL(@interval, 1), [Date])
              FROM Dates
              WHERE Date < @EndDate)
              INSERT INTO @Dates
                     SELECT [Date]
                     FROM Dates
                     OPTION(MAXRECURSION 900);
         RETURN;
     END;

回复收藏 0 原文

凉栀 2024-12-17 13:19:41

Declare @StartDate datetime = '2015-01-01'
Declare @EndDate datetime = '2016-12-01'
declare @DaysInMonth int
declare @tempDateRange Table
(
DateFrom datetime,
DateThru datetime
);

While @StartDate<=@EndDate
begin
    SET @DaysInMonth=DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,@StartDate),0)))

    IF DAY(@StartDate)=1 
        SET @EndDate=DATEADD(DAY,14,@StartDate)
    ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=30
        SET @EndDate=DATEADD(DAY,14,@StartDate)
    ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=31
        SET @EndDate=DATEADD(DAY,15,@StartDate)
    ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=28
        SET @EndDate=DATEADD(DAY,12,@StartDate)
    ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=29
        SET @EndDate=DATEADD(DAY,13,@StartDate)

    INSERT INTO @tempDateRange (DateFrom,DateThru)
    VALUES 
     (
        @StartDate,
        @EndDate
     )

    SET @StartDate=DATEADD(DAY,1,@EndDate)

    IF @EndDate< '2016-12-31'
     IF DAY(@StartDate)=1 
        SET @EndDate=DATEADD(DAY,14,@StartDate)
     ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=30
        SET @EndDate=DATEADD(DAY,14,@StartDate)
     ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=31
        SET @EndDate=DATEADD(DAY,15,@StartDate)
     ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=28
        SET @EndDate=DATEADD(DAY,12,@StartDate)
     ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=29
        SET @EndDate=DATEADD(DAY,13,@StartDate)
end ;

select * from @tempDateRange

+++++++++++++++++++++++++++++
Result:
DateFrom |DateThru

Declare @StartDate datetime = '2015-01-01'
Declare @EndDate datetime = '2016-12-01'
declare @DaysInMonth int
declare @tempDateRange Table
(
DateFrom datetime,
DateThru datetime
);

While @StartDate<=@EndDate
begin
    SET @DaysInMonth=DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,@StartDate),0)))

    IF DAY(@StartDate)=1 
        SET @EndDate=DATEADD(DAY,14,@StartDate)
    ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=30
        SET @EndDate=DATEADD(DAY,14,@StartDate)
    ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=31
        SET @EndDate=DATEADD(DAY,15,@StartDate)
    ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=28
        SET @EndDate=DATEADD(DAY,12,@StartDate)
    ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=29
        SET @EndDate=DATEADD(DAY,13,@StartDate)

    INSERT INTO @tempDateRange (DateFrom,DateThru)
    VALUES 
     (
        @StartDate,
        @EndDate
     )

    SET @StartDate=DATEADD(DAY,1,@EndDate)

    IF @EndDate< '2016-12-31'
     IF DAY(@StartDate)=1 
        SET @EndDate=DATEADD(DAY,14,@StartDate)
     ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=30
        SET @EndDate=DATEADD(DAY,14,@StartDate)
     ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=31
        SET @EndDate=DATEADD(DAY,15,@StartDate)
     ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=28
        SET @EndDate=DATEADD(DAY,12,@StartDate)
     ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=29
        SET @EndDate=DATEADD(DAY,13,@StartDate)
end ;

select * from @tempDateRange

+++++++++++++++++++++++++++++
Result:
DateFrom |DateThru

回复收藏 0 原文

倥絔 2024-12-17 13:19:41

CREATE table #ProductSales (ProjectID Int, ProjectName varchar(100), TotalBillableFees Money, StartDate Date, EndDate Date, DataDate Date)

  Insert into #ProductSales
  Values
  (373104,'Product Sales - Flex Creation Test',40000.00,'2019-04-01','2020-06-01','2019-08-01'),
  (375111,'Product Sales - SMART',40000.00,'2019-04-01','2019-09-01','2019-08-01')

  ;WITH Dates AS (
        SELECT ProjectiD
        ,Convert(decimal(10,2),TotalBillableFees/IIF(DATEDIFF(MONTH,StartDate,EndDate)=0,1,DATEDIFF(MONTH,StartDate,EndDate))) AS BillableFeesPerMonths,EndDate
         ,[Date] = CONVERT(DATETIME,EOMONTH(StartDate))
         FROM #ProductSales
        UNION ALL SELECT ProjectiD,BillableFeesPerMonths,EndDate,
         [Date] = DATEADD(MONTH, 1, [Date])
        FROM
         Dates
        WHERE
         Date < EOMONTH(EndDate)
) SELECT ProjectID,BillableFeesPerMonths,
 CAST([Date] as Date) Date
FROM
 Dates
 OPTION (MAXRECURSION 45)

CREATE table #ProductSales (ProjectID Int, ProjectName varchar(100), TotalBillableFees Money, StartDate Date, EndDate Date, DataDate Date)

  Insert into #ProductSales
  Values
  (373104,'Product Sales - Flex Creation Test',40000.00,'2019-04-01','2020-06-01','2019-08-01'),
  (375111,'Product Sales - SMART',40000.00,'2019-04-01','2019-09-01','2019-08-01')

  ;WITH Dates AS (
        SELECT ProjectiD
        ,Convert(decimal(10,2),TotalBillableFees/IIF(DATEDIFF(MONTH,StartDate,EndDate)=0,1,DATEDIFF(MONTH,StartDate,EndDate))) AS BillableFeesPerMonths,EndDate
         ,[Date] = CONVERT(DATETIME,EOMONTH(StartDate))
         FROM #ProductSales
        UNION ALL SELECT ProjectiD,BillableFeesPerMonths,EndDate,
         [Date] = DATEADD(MONTH, 1, [Date])
        FROM
         Dates
        WHERE
         Date < EOMONTH(EndDate)
) SELECT ProjectID,BillableFeesPerMonths,
 CAST([Date] as Date) Date
FROM
 Dates
 OPTION (MAXRECURSION 45)

回复收藏 0 原文

吾家有女初长成 2024-12-17 13:19:41

当我们无法在数据库中创建函数时，递归查询是一个很好的选择。

MySQL 8+ & MariaDB 10.2.2+

  WITH RECURSIVE dates AS (
    SELECT '2022-01-01' AS _day -- Your start date
    UNION ALL
    SELECT DATE_ADD(_day, INTERVAL 1 DAY)
    FROM dates
    WHERE _day < '2022-10-12'   -- Your end date
  )

Postgres 11+

  WITH RECURSIVE dates AS (
    SELECT DATE('2022-01-01') AS _day -- Your start date
    UNION ALL
    SELECT DATE(_day + INTERVAL '1 day')
    FROM dates
    WHERE _day < '2022-10-12'   -- Your end date
  )

要在 SELECT 语句中连接这些日期，您可以使用 JOIN days ON true 来复制日期范围内每个日期的行。

  [WITH statement according to your database]
  SELECT col1, col2, _day
  FROM my_table
  JOIN dates ON true

Recursive query is a good alternative when we cannot create functions in the database.

MySQL 8+ & MariaDB 10.2.2+

  WITH RECURSIVE dates AS (
    SELECT '2022-01-01' AS _day -- Your start date
    UNION ALL
    SELECT DATE_ADD(_day, INTERVAL 1 DAY)
    FROM dates
    WHERE _day < '2022-10-12'   -- Your end date
  )

Postgres 11+

  WITH RECURSIVE dates AS (
    SELECT DATE('2022-01-01') AS _day -- Your start date
    UNION ALL
    SELECT DATE(_day + INTERVAL '1 day')
    FROM dates
    WHERE _day < '2022-10-12'   -- Your end date
  )

To join these dates in your SELECT statement, you can use a JOIN dates ON true to replicate your rows for each date in your date range.

  [WITH statement according to your database]
  SELECT col1, col2, _day
  FROM my_table
  JOIN dates ON true

回复收藏 0 原文

飘然心甜 2024-12-17 13:19:41

我在 Databricks 上使用 ANSI 标准 SQL 方言，下面的代码是创建月份行的更简单方法：

SELECT
  EXPLODE(
    SEQUENCE(
      CURRENT_DATE() + MAKE_INTERVAL(0, -11),
      CURRENT_DATE() + MAKE_INTERVAL(0, 1),
      INTERVAL 1 MONTH))

I use the ANSI standard SQL dialect on Databricks and the code below it`s a simpler way of creating month rows:

SELECT
  EXPLODE(
    SEQUENCE(
      CURRENT_DATE() + MAKE_INTERVAL(0, -11),
      CURRENT_DATE() + MAKE_INTERVAL(0, 1),
      INTERVAL 1 MONTH))

回复收藏 0 原文

悍妇囚夫 2024-12-17 13:19:41

如果您的数据库支持递归公用表表达式，那么这很容易做到。

这是 MySQL 8 上的示例。

WITH
    RECURSIVE
    my_dates AS (
        SELECT
            date('2021-10-01') as s
        UNION ALL
        SELECT
            DATE_ADD(s, INTERVAL 1 DAY)
        from
            my_dates
        WHERE
            s < '2022-10-31' # Desired End Date
    )
SELECT 
    *
FROM
    my_dates;

例如，如果您希望日期向后计数，则可以使用 DATE_SUB() 并相应地调整 where 子句。您还可以根据需要将日期增加一天以上，只需以一天以上的间隔进行日期计算即可。

参考：
https://nucco.org/2023/ 02/生成a-series-of-dates-in-sql.html

This is pretty easy to do if your database supports recursive common table expressions.

Here is an example on MySQL 8.

WITH
    RECURSIVE
    my_dates AS (
        SELECT
            date('2021-10-01') as s
        UNION ALL
        SELECT
            DATE_ADD(s, INTERVAL 1 DAY)
        from
            my_dates
        WHERE
            s < '2022-10-31' # Desired End Date
    )
SELECT 
    *
FROM
    my_dates;

You can use DATE_SUB() if you want the dates to count backwards, for example and adjust the where clause accordingly. You can also increment the dates by more than one day as desired by simply doing the date calculation with more than one day interval.

Reference:
https://nucco.org/2023/02/generate-a-series-of-dates-in-sql.html

回复收藏 0 原文

生成日期范围之间的日期

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

评论（15）

MySQL 8+ & MariaDB 10.2.2+

Postgres 11+

MySQL 8+ & MariaDB 10.2.2+

Postgres 11+

关于作者

相关话题

热门标签

推荐作者

已经忘了多久

15867725375

LonelySnow

走过海棠暮

轻许诺言

信馬由缰

友情链接

生成日期范围之间的日期

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

评论（15）

MySQL 8+ & MariaDB 10.2.2+

Postgres 11+

MySQL 8+ & MariaDB 10.2.2+

Postgres 11+

关于作者

相关话题

热门标签

推荐作者

已经忘了多久

15867725375

LonelySnow

走过海棠暮

轻许诺言

信馬由缰

友情链接

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。