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