在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屋！