在SQL Server中的两个日期之间创建日期范围 [英] Create a date range between two dates in SQL server
问题描述
我有一张如下表格
I have a table like below
ACADEMIC_TERM ACADEMIC_YEAR START_DATE END_DATE
FALL 2014 2014-10-12 00:00:00.000 2015-01-22 00:00:00.000
FALL 2015 2015-09-13 00:00:00.000 2016-01-05 00:00:00.000
SPRG 2015 2015-02-15 00:00:00.000 2015-05-28 00:00:00.000
我想延长日期范围
如下所示
I want to extend the date range
like below
DATE ACADEMIC_YEAR ACADEMIC_TERM
12-10-2014 2014 FALL
13-10-2014 2014 FALL
---------- ------ ----
---------- ------ ----
---------- ------ ----
15-02-2015 2015 SPRG
16-02-2015 2015 SPRG
---------- ------ ----
---------- ------ ----
13-09-2015 2015 FALL
---------- ------ ----
---------- ------ ----
---------- ------ ----
日期应自动创建每个学期的开始和结束日期。我怎么能这样做?
我尝试过:
我创建了第一个术语的开始日期和getdate()作为最后一个日期之间的日期,但它无法识别哪个术语是日期。对于今年我可以简单地采取Datepart(日期)
the dates should create automatic with start and end date of each term. How can I do this?
What I have tried:
I created the dates between the start date of the first term and getdate() as the last date but it cannot identify which term is the date. For year I can simply take the Datepart(date)
推荐答案
我建议使用 CTE [ ^ ], - 简称 - 表示:递归查询。
看看例子:
I'd recommend to use CTE[^], which - in a short - means: a recursive query.
Take a look at example:
DECLARE @tmp TABLE(ACADEMIC_TERM VARCHAR(50), ACADEMIC_YEAR INT, [START_DATE] DATETIME, END_DATE DATETIME)
INSERT INTO @tmp (ACADEMIC_TERM, ACADEMIC_YEAR, [START_DATE], END_DATE)
VALUES('FALL', 2014, '2014-10-12 00:00:00.000', '2015-01-22 00:00:00.000'),
('FALL', 2015, '2015-09-13 00:00:00.000', '2016-01-05 00:00:00.000'),
('SPRG', 2015, '2015-02-15 00:00:00.000', '2015-05-28 00:00:00.000')
;WITH CTE AS
(
--initial query
SELECT ROW_NUMBER() OVER(ORDER BY ACADEMIC_YEAR) AS RowNo, 1 AS IterationID, ACADEMIC_TERM, ACADEMIC_YEAR, [START_DATE] AS CurrDate, END_DATE
FROM @tmp
--recursive part
UNION ALL
SELECT RowNo, IterationID +1, ACADEMIC_TERM, ACADEMIC_YEAR, DATEADD(dd, 1, CurrDate) AS CurrDate, END_DATE
FROM CTE
WHERE DATEADD(dd, IterationID, CurrDate)<=END_DATE
)
SELECT CurrDate, ACADEMIC_TERM, ACADEMIC_YEAR
FROM CTE
ORDER BY RowNo, IterationID
如需了解更多信息,请参阅: DATEADD(Transact-SQL)| Microsoft Docs [ ^ ]
祝你好运!
For further information, please, see: DATEADD (Transact-SQL) | Microsoft Docs[^]
Good luck!
我重新创建了你的表格为@AcadmicDates并如下所示填充
I have recreated your table as @AcadmicDates and populated as such like below
DECLARE @AcademicDates TABLE (
AcademicTerm VARCHAR(6) NULL,
AcademicYear INT NULL,
StartDate DATE NULL,
EndDate DATE NULL
)
INSERT @AcademicDates
VALUES ('Fall', 2014, '10/12/2014', '01/22/2015')
, ('Fall', 2015, '09/13/2015', '01/05/2016')
, ('Spring', 2015, '02/15/2015', '05/28/2015')
你的部分o f扩展日期范围确实是一个简单的查询:
Your portion of "extending the date" range really is a simple query:
SELECT AcademicYear, AcademicTerm
FROM @AcademicDates
WHERE '10/12/2014' BETWEEN StartDate AND EndDate
并且可用于填充辅助表
And could be used to populate a secondary table
DECLARE @DateToAcademicDate TABLE (
DateToCheck DATE NULL,
AcademicYear INT NULL,
AcademicTerm VARCHAR(6) NULL
)
DECLARE @RangeDate DATE = '10/12/2014'
WHILE (@RangeDate <= '01/05/2016') BEGIN
IF EXISTS(SELECT 1 FROM @AcademicDates WHERE @RangeDate BETWEEN StartDate AND EndDate) BEGIN
INSERT @DateToAcademicDate
SELECT @RangeDate, AcademicYear, AcademicTerm
FROM @AcademicDates
WHERE @RangeDate BETWEEN StartDate AND EndDate
END
SET @RangeDate = DATEADD(dd, 1, @RangeDate)
END
将返回以下内容
Which will return the following
DateToCheck AcademicYear AcademicTerm
----------- ------------ ------------
2014-10-12 2014 Fall
... 2014 Fall
2015-01-22 2014 Fall
2015-02-15 2015 Spring
... 2015 Spring
2015-05-28 2015 Spring
2015-09-13 2015 Fall
... 2015 Fall
2016-01-05 2015 Fall
您可以使用此查询在范围之间创建日期
You can create a Date between range using this query
Declare @StartDay datetime, @EndDay datetime
Set @StartDay='2018-01-01'
Set @EndDay='2018-12-31'
;with cte(Date) as
(
select @StartDay
union all
select Date+1 from cte where Date < @EndDay
)
select Date,DATENAME(W,Date)Day from cte option (MAXRECURSION 400)
这篇关于在SQL Server中的两个日期之间创建日期范围的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!