how to get between using two dates in sql server

narasiman rao

9y1.1k

fromdate todate
2017-03-02 2017-03-09

from the above table i want the ouput as follows

2017-03-03
2017-03-04
2017-03-05
2017-03-06
2017-03-07
2017-03-08

for that how to write the query in sql server

4 Replies

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

Hariharan Krishnamoorthi9y ago
1. DECLARE @fromdate DATE
2. DECLARE @todate DATE
4. SET @fromdate='2017-03-02';
5. SET @todate='2017-03-09';
7. select * from TestCalendar
8. where
9. DateVal between DATEADD(D, 0,DATEDIFF(D,-1,@fromdate)) AND
10. DATEADD(D,-1,DATEDIFF(D,0,@todate))
0

Tapan Patel9y ago

In addition to CTE (recursive) approach, you can go for below two different ways to do that. please test it out from performance perspective.

DECLARE @dt1 DATE = '5-21-2017'
DECLARE @dt2 DATE = '5-27-2017'
CREATE TABLE #tempTable(id INT,wDate DATE)
-- as sys.columns would have some rows always. Also note that this won't work if
-- you specify huge date range -ranging more than number of rows in --sys.columns table
INSERT INTO #tempTable
SELECT TOP (DATEDIFF(DAY,@dt1,@dt2)-1) ROW_NUMBER() OVER (ORDER BY (SELECT 1)) [c],null
FROM sys.columns
UPDATE #tempTable
SET wDate=DATEADD(DAY,id,@dt1)
SELECT * FROM #tempTable
DROP TABLE #tempTable

Or you can rely on loops to do that.

DECLARE @dt1 DATE = '5-21-2017'
DECLARE @dt2 DATE = '5-27-2017'
DECLARE @counter INT=1;
CREATE TABLE #tempTable(wDate DATE)
Set @dt2 = DATEADD(DAY,-1,@dt2) -- setting to second last date
WHILE (@dt1<@dt2)
BEGIN
SELECT @dt1 = DATEADD(DAY,@counter,@dt1)
INSERT INTO #tempTable
( wDate )
VALUES ( @dt1 -- wDate - date
)
END
SELECT * FROM #tempTable
DROP TABLE #tempTable

Midhun Tp9y ago

Hi,

Please try below query -

DECLARE @fromdate DATETIME
DECLARE @todate DATETIME
SET @fromdate='2017-03-02';
SET @todate='2017-03-09';
WITH mycte AS
(
SELECT dateadd(dd,1,@fromdate) DateValue
UNION ALL
SELECT DateValue + 1
FROM mycte
WHERE DateValue + 1 < @todate
)
SELECT convert(DATE,DateValue,120) AS datevalue FROM mycte

Reference :

https://stackoverflow.com/questions/1378593/get-a-list-of-dates-between-two-dates-using-a-function

U Thu9y ago

datediff function using fri.

-1