SQL 获取日期范围之间的日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/271595/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Getting Dates between a range of dates
提问by Incidently
I need to get all the dates present in the date range using SQL Server 2005
我需要使用 SQL Server 2005 获取日期范围内的所有日期
回答by Incidently
Here you go:
干得好：
DECLARE @DateFrom smalldatetime, @DateTo smalldatetime;
SET @DateFrom='20000101';
SET @DateTo='20081231';
-------------------------------
WITH T(date)
AS
(
SELECT @DateFrom
UNION ALL
SELECT DateAdd(day,1,T.date) FROM T WHERE T.date < @DateTo
)
SELECT date FROM T OPTION (MAXRECURSION 32767);
回答by Ed Guiness
If you have the dates in a table and simply want to select those between two dates you can use
如果您在表格中有日期并且只想在两个日期之间选择日期，您可以使用
select * from yourTable where yourDate between date1 and date2
If you want to produce the dates from nothing you could do it with a loop or you could populate a temporary table with dates and then select from that.
如果您想从零生成日期，您可以使用循环来完成，或者您可以使用日期填充临时表，然后从中进行选择。
回答by Surinder Singh
DECLARE @Date1 DATE='2016-12-21', @Date2 DATE='2016-12-25'
SELECT DATEADD(DAY,number,@Date1) [Date] FROM master..spt_values WHERE type = 'P' AND DATEADD(DAY,number,@Date1) <= @Date2
回答by Chris Moutray
Slightly more complicated but perhaps more flexible would be to make use of a table containing a sequential set of numbers. This allows for more than one date range with different intervals.
稍微复杂一点但也许更灵活的是使用包含一组连续数字的表格。这允许具有不同间隔的多个日期范围。
/* holds a sequential set of number ie 0 to max */
/* where max is the total number of rows expected */
declare @Numbers table ( Number int )
declare @max int
declare @cnt int
set @cnt = 0
/* this value could be limited if you knew the total rows expected */
set @max = 999
/* we are building the NUMBERS table on the fly */
/* but this could be a proper table in the database */
/* created at the point of first deployment */
while (@cnt <= @max)
begin
insert into @Numbers select @cnt
set @cnt = @cnt + 1
end
/* EXAMPLE of creating dates with different intervals */
declare @DateRanges table (
StartDateTime datetime, EndDateTime datetime, Interval int )
/* example set of date ranges */
insert into @DateRanges
select '01 Jan 2009', '10 Jan 2009', 1 /* 1 day interval */
union select '01 Feb 2009', '10 Feb 2009', 2 /* 2 day interval */
/* heres the important bit generate the dates */
select
StartDateTime
from
(
select
d.StartDateTime as RangeStart,
d.EndDateTime as RangeEnd,
dateadd(DAY, d.Interval * n.Number, d.StartDateTime) as StartDateTime
from
@DateRanges d, @Numbers n
) as dates
where
StartDateTime between RangeStart and RangeEnd
order by StartDateTime
I actully use a variation of this to split dates into time slots (with various intervals but usually 5 mins long). My @numbers table contains a max of 288 since thats the total number of 5 min slots you can have in a 24 hour period.
我实际上使用它的一个变体将日期分成时间段（具有不同的间隔，但通常为 5 分钟长）。我的@numbers 表最多包含 288 个，因为这是您在 24 小时内可以拥有的 5 分钟插槽总数。
/* EXAMPLE of creating times with different intervals */
delete from @DateRanges
/* example set of date ranges */
insert into @DateRanges
select '01 Jan 2009 09:00:00', '01 Jan 2009 12:00:00', 30 /* 30 minutes interval */
union select '02 Feb 2009 09:00:00', '02 Feb 2009 10:00:00', 5 /* 5 minutes interval */
/* heres the import bit generate the times */
select
StartDateTime,
EndDateTime
from
(
select
d.StartDateTime as RangeStart,
d.EndDateTime as RangeEnd,
dateadd(MINUTE, d.Interval * n.Number, d.StartDateTime) as StartDateTime,
dateadd(MINUTE, d.Interval * (n.Number + 1) , StartDateTime) as EndDateTime
from
@DateRanges d, @Numbers n
) as dates
where
StartDateTime >= RangeStart and EndDateTime <= RangeEnd
order by StartDateTime
回答by user34850
Here's Oracle version of date generation:
这是日期生成的 Oracle 版本：
SELECT TO_DATE ('01-OCT-2008') + ROWNUM - 1 g_date
FROM all_objects
WHERE ROWNUM <= 15
instead of all_objects it can be any table with enough rows to cover the required range.
而不是 all_objects 它可以是任何具有足够行数以覆盖所需范围的表。
回答by Soraz
If what you want is to get all dates present in your database between two dates (i.e. what dates have customers placed orders in Q3 of 2008) you would write something like this:
如果您想要获取数据库中两个日期之间的所有日期（即客户在 2008 年第三季度下订单的日期），您可以编写如下内容：
select distinct(orderPlacedDate)
from orders
where orderPlacedDate between '2008-07-01' and 2008-09-30'
order by orderPlacedDate
回答by ConcernedOfTunbridgeWells
To generate a range of dates you could write a table-valued function. This is a function that creates a date dimension for a data warehouse - you could probably adapt it fairly readily by trimming out the specials.
要生成一系列日期，您可以编写一个表值函数。这是一个为数据仓库创建日期维度的函数 - 您可能可以通过修剪特殊内容来相当容易地调整它。
Edit: Here it is without the date dimension hierarchy.
编辑：这里没有日期维度层次结构。
if object_id ('ods.uf_DateHierarchy') is not null
drop function ods.uf_DateHierarchy
go
create function ods.uf_DateHierarchy (
@DateFrom datetime
,@DateTo datetime
) returns @DateHierarchy table (
DateKey datetime
) as begin
declare @today datetime
set @today = @Datefrom
while @today <= @DateTo begin
insert @DateHierarchy (DateKey) values (@today)
set @today = dateadd (dd, 1, @today)
end
return
end
go
