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, 0,DATEDIFF(D,-1,@fromdate)) AND DATEADD(D,-1,DATEDIFF(D,0,@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 Tp · 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(dd,1,@fromdate) DateValue UNION ALL SELECT DateValue + 1 FROM mycte WHERE DateValue + 1 ) 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 Thu · Answer

datediff function using fri.

how to get between using two dates in sql server

Answers (4)