在日期范围之间生成日期 [英] Generate Dates between date ranges

问题描述

我需要填充一个表格来存储两个给定日期之间的日期范围: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

因此，在这种情况下，该表将从 11 年 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.

这篇关于在日期范围之间生成日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！