blocks|key|1695794|text|如果您在表中有日期，并且只想选择介于两个日期之间的日期，则可以使用|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1695795|select+*+from+yourTable+where+yourDate+between+date1+and+date2|code-block|syntax|javascript|1695796|如果您想从无到有地生成日期，您可以使用循环来实现，或者您可以用日期填充一个临时表，然后从中进行选择。|1695797|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

If you have the dates in a table and simply want to select those between two dates you can use

<pre><code>select * from yourTable where yourDate between date1 and date2
</code></pre>

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.

blocks|key|1695842|text|要生成一系列日期，您可以编写一个表值函数。这是一个为数据仓库创建日期维度的函数-您可以很容易地通过裁剪特殊项来调整它。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1695843|编辑:这里没有日期维度层次结构。|1695844|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|code-block|syntax|javascript|1695845|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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.

<pre><code>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 &lt;= @DateTo begin
 insert @DateHierarchy (DateKey) values (@today)
 set @today = dateadd (dd, 1, @today)
 end

 return
end

go
</code></pre>

blocks|key|1695850|text|这就是了：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1695851|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);|code-block|syntax|javascript|1695852|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Here you go:

<pre><code>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 &lt; @DateTo
)
SELECT date FROM T OPTION (MAXRECURSION 32767);
</code></pre>

blocks|key|1480743|text|如果您想要获得数据库中两个日期之间的所有日期(即客户在2008年的Q3中下单的日期)，您可以编写如下代码：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1480744|select+distinct(orderPlacedDate)+
from+orders+
where+orderPlacedDate+between+'2008-07-01'+and+2008-09-30'+
order+by+orderPlacedDate|code-block|syntax|javascript|1480745|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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:

<pre><code>select distinct(orderPlacedDate) 
from orders 
where orderPlacedDate between '2008-07-01' and 2008-09-30' 
order by orderPlacedDate
</code></pre>

blocks|key|1480797|text|以下是Oracle版本的日期生成：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1480798|SELECT+TO_DATE+('01-OCT-2008')+%2B+ROWNUM+-+1+g_date
++FROM+all_objects
+WHERE+ROWNUM+<=+15|code-block|syntax|javascript|1480799|它可以是任何具有足够行来覆盖所需范围的表，而不是all_objects。|1480800|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Here's Oracle version of date generation:

<pre><code>SELECT TO_DATE ('01-OCT-2008') + ROWNUM - 1 g_date
 FROM all_objects
 WHERE ROWNUM &lt;= 15
</code></pre>

instead of all_objects it can be any table with enough rows to cover the required range.

blocks|key|1480874|text|稍微复杂一点，但可能更灵活的是使用一个包含一组连续数字的表。这允许具有不同间隔的多个日期范围。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1480875|/*+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+%2B+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|code-block|syntax|javascript|1480876|我实际上使用了一个变体来将日期划分为多个时间段(时间间隔各不相同，但通常为5分钟长)。My+@numbers表最多包含288个，因为这是您在24小时内可以拥有的5分钟时段的总数。|1480877|/*+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+%2B+1)+,+StartDateTime)+as+EndDateTime
++++++from+
++++++++++++@DateRanges+d,+@Numbers+n
)+as+dates
where
++++++StartDateTime+>=+RangeStart+and+EndDateTime+<=+RangeEnd
order+by+StartDateTime|1480878|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

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.

<pre><code>/* 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 &lt;= @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
</code></pre>

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.

<pre><code>/* 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 &gt;= RangeStart and EndDateTime &lt;= RangeEnd
order by StartDateTime
</code></pre>

blocks|key|1480895|text|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|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1480896|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>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) &lt;= @Date2
</code></pre>

I need to get all the dates present in the date range using SQL Server 2005

Getting Dates between a range of dates

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

我需要使用SQL Server 2005获取日期范围内的所有日期

问获取日期范围内的日期
EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问获取日期范围内的日期EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问获取日期范围内的日期
EN