How to Get all Dates Between Two Dates in SQL

jaymin sathavara

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

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Mohanty4y ago
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 <= @count1
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 <= @ncount1
    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
+2
Asma Khalid4y ago
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 < @edate
    )

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