How to Get all Dates Between Two Dates in SQL

Question

I want to display all the dates between two dates for a particular record

Table.

JWDID	FromDate	ToDate	Quantity
100	2022-06-01	2022-06-05	1000
101	2022-06-03	2022-06-03	1500
102	2022-06-29	2022-07-03	1800

I need to output all dates with Quantity between fromDate & To Date.

Where FromDate >= '2022-06-01' AND ToDate <= '2022-06-30'

Expected output :

JWDID	Date	FromDate	ToDate	Quantity
100	2022-06-01	2022-06-01	2022-06-05	1000
100	2022-06-02	2022-06-01	2022-06-05	1000
100	2022-06-03	2022-06-01	2022-06-05	1000
100	2022-06-04	2022-06-01	2022-06-05	1000
100	2022-06-05	2022-06-01	2022-06-05	1000
101	2022-06-03	2022-06-03	2022-06-03	1500

guide me to write a query

Amit Mohanty · Answer

Check the below queries DECLARE @ table TABLE (sqlid INT IDENTITY, JWDID NVARCHAR(150), FromDate DATE , ToDate DATE , Quantity INT ) INSERT INTO @ table SELECT JWDID, FromDate, ToDate, Quantity FROM YourTable WHERE FromDate >= '2022-06-01' AND ToDate '2022-06-30' IF OBJECT_ID( 'tempdb..#temp1' ) IS NOT NULL DROP TABLE #temp1 CREATE TABLE #temp1 ([JWDID] NVARCHAR(10), [ Date ] DATE , [FromDate] DATE , [ToDate] DATE , [Quantity] INT ) DECLARE @ count INT = 1, @count1 INT = ( SELECT COUNT (*) FROM @ table ) WHILE @ count BEGIN DECLARE @MinDate DATE = ( SELECT FromDate FROM @ table WHERE sqlid = @ count ) DECLARE @MaxDate DATE = ( SELECT ToDate FROM @ table WHERE sqlid = @ count ) IF OBJECT_ID( 'tempdb..#temp2' ) IS NOT NULL DROP TABLE #temp2 CREATE TABLE #temp2 (sqlid INT IDENTITY, [ Date ] DATE ) INSERT INTO #temp2 SELECT TOP (DATEDIFF( DAY , @MinDate, @MaxDate) + 1) Date = DATEADD( DAY , ROW_NUMBER() OVER( ORDER BY a.object_id) - 1, @MinDate) FROM sys.all_objects a CROSS JOIN sys.all_objects b DECLARE @ncount INT = 1, @ncount1 INT = ( SELECT COUNT (*) FROM #temp2) WHILE @ncount BEGIN DECLARE @ Date DATE = ( SELECT [ Date ] FROM #temp2 WHERE sqlid = @ncount) INSERT INTO #temp1 SELECT JWDID, @ Date , FromDate, ToDate, Quantity FROM @ table WHERE sqlid = @ count SET @ncount = @ncount + 1 END SET @ count = @ count + 1 END SELECT * FROM #temp1 You can use the above query as stored procedure also

Asma Khalid · Answer

Use below code to generate dates between provided dates i.e. DECLARE @sdate DATE = '2022-06-01' ,@edate DATE = '2022-06-05' ; WITH dates_CTE ( DATE ) as ( SELECT @sdate UNION ALL SELECT DATEADD( day , 1, DATE ) FROM dates_CTE WHERE DATE ) SELECT date FROM dates_CTE Mark answer as accpted if helpful Thanks & Regards, Asma Khalid For More Visit: www.asmak9.com or www.bytezaar.com

How to Get all Dates Between Two Dates in SQL

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (2)