Create a date range between two dates in SQL server

Question

0.00/5 (No votes)

See more:

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
     ----------      ------              ----
     ----------      ------              ----
     ----------      ------              ----

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)

Posted 18-Sep-18 3:44am

binu.emiliya

Updated 18-Sep-18 22:48pm

Add a Solution

3 solutions

Solution 3

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)

Posted 18-Sep-18 22:48pm

kirthiga S

Comments

Maciej Los 19-Sep-18 6:39am

This solution is partially helpful only. Take a look at date ranges... They're different. It isn't simple date-from to date-to, but the set of them. Please, see my answer.

binu.emiliya 19-Sep-18 6:42am

Thank you Krithiga. This I have already done

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2018-09-18T10:45:00

Solution 2

I'd recommend to use CTE[^], which - in a short - means: a recursive query.

Take a look at example:

SQL

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

For further information, please, see: DATEADD (Transact-SQL) | Microsoft Docs[^]

Good luck!

Posted 18-Sep-18 10:45am

Maciej Los

Comments

binu.emiliya 19-Sep-18 6:41am

This is working fine Thank you so much for your help

Maciej Los 19-Sep-18 6:42am

You're very welcome.

binu.emiliya 25-Sep-18 3:17am

Dear Maciej Los,

I am using this code now, but it is not returning last month data, say example Spring 2017 last date is 31-Dec-2107 but the code is getting data up to nov only.

Maciej Los 25-Sep-18 3:38am

This should work fine, even for 2017-12-31. Please, check out if date is in correct format.

binu.emiliya 25-Sep-18 4:27am

'2014-10-13 00:00:00.000' this is the date format I am using, and I am storing this code in a view so I am using TOP 100 PERCENT in this code. But I am not getting the last month of each semester dates.

Thank you for your time

Maciej Los 25-Sep-18 4:38am

Well, i have no idea why you don't get last month of each semester dates... Note, that i don't have an access to your HDD or SQL server. As you know, above CTE query works fine for sample data, so... is should also works for real data.

binu.emiliya 25-Sep-18 4:43am

Ok, Thank you!

Member 4280314 1-Jul-19 13:16pm

The where in the recursive part should be as follows, in order to return all dates:

WHERE DATEADD(dd, 1, CurrDate)<=END_DATE

MadMyche · Accepted Answer · 2018-09-18T05:20:00

I have recreated your table as @AcadmicDates and populated as such like below

SQL

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')

Your portion of "extending the date" range really is a simple query:

SQL

SELECT  AcademicYear, AcademicTerm
FROM    @AcademicDates
WHERE   '10/12/2014' BETWEEN StartDate AND EndDate

And could be used to populate a secondary table

SQL

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