生成日期范围之间的日期

2022-01-24 00:00:00 sql range tsql sql-server-2008 sql-server

我需要填充一个表格来存储两个给定日期之间的日期范围:09/01/11 - 10/10/11

I need to populate a table that will store the date ranges between 2 given dates: 09/01/11 - 10/10/11

所以在这种情况下，表格将从 2011 年 9 月 1 日开始并每天存储到 11 年 10 月 10 日我想知道在 SQL Server 中是否有一种巧妙的方法来执行此操作 - 我目前正在使用 SQL Server 2008.谢谢

So in this case the table would start from 09/01/11 and store each day till it got to 10/10/11 I was wondering if there was a slick way of doing this in SQL Server - I am currently using SQL Server 2008. Thanks

推荐答案

Easy on SQL 2005+;如果您有数字或计数表，则更容易.我在下面伪造了它:

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.

由于人们似乎对计数表有疑问，让我用一个从零开始的计数表重写它.首先，这里有一些创建和填充表格的代码.

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

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

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)

高性能，无递归.

相关文章