how to get between using two dates in sql server

Question

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

Hariharan Krishnamoorthi · Answer

DECLARE @fromdate DATE DECLARE @todate DATE SET @fromdate= '2017-03-02' ; SET @todate= '2017-03-09' ; select * from TestCalendar where DateVal between DATEADD(D 0DATEDIFF(D-1@fromdate)) AND DATEADD(D-1DATEDIFF(D0@todate))

Tapan Patel · Answer

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 BEGIN SELECT @dt1 = DATEADD( DAY @counter@dt1) INSERT INTO #tempTable ( wDate ) VALUES ( @dt1 -- wDate - date ) END SELECT * FROM #tempTable DROP TABLE #tempTable

Midhun T P · Answer

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(dd1@fromdate) DateValue UNION ALL SELECT DateValue + 1 FROM mycte WHERE DateValue + 1 ) SELECT convert ( DATE DateValue120) AS datevalue FROM mycte Reference : https://stackoverflow.com/questions/1378593/get-a-list-of-dates-between-two-dates-using-a-function

U Thu · Answer

datediff function using fri.