CODEWITHSUNDEEP
sql-serversql-server-2008
Brian Crist
Brian Crist

Reputation: 814

How to get days from date to date in SQL Server?

In SQL Server, I want to get days from date to date. Example: from 2015/12/28 to 2016/01/02, the result as 

2015/12/28
2015/12/29
2015/12/30
2015/12/31
2016/01/01
2016/01/02

DECLARE @STARTDATE DATETIME = '2015-12-28'
DECLARE @ENDDATE DATETIME = '2016-01-02'
SELECT BETWEEN @STARTDATE AND @ENDDATE AS DAYS

Upvotes: 1

Views: 97

Answers (4)

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

Use CTE

DECLARE @STARTDATE DATE = '2015-12-28'
DECLARE @ENDDATE DATE = '2016-01-02'

;WITH CTE AS
(
   SELECT @STARTDATE As dt
   UNION ALL
   SELECT DATEADD(D,1,dt) AS dt
   FROM CTE
   WHERE dt < @ENDDATE   
)
SELECT * FROM CTE

Upvotes: 4

BJones
BJones

Reputation: 2460

You could build a calendar table, which would probably come in handy down the road. Or you could use a loop.

DECLARE @ENDDATE DATETIME = '2016-01-02'
DECLARE @DAY DATETIME = '2015-12-28'
WHILE @Day <= @ENDDATE
BEGIN
SELECT @DAY
SET @DAY = DATEADD(DD,1,@DAY)
END

Or for all of the days in one result set:

DECLARE @ENDDATE DATETIME = '2016-01-02'
DECLARE @DAY DATETIME = '2015-12-28'
DECLARE @TABLE TABLE (DATE DATETIME)
WHILE @Day <= @ENDDATE
BEGIN
INSERT @TABLE
VALUES (@DAY)
SET @DAY = DATEADD(DD,1,@DAY)
END

SELECT *
FROM @TABLE

Upvotes: 3

TheGameiswar
TheGameiswar

Reputation: 28890

using Numbers table

select dateadd(day,n,startdate)
from numbers
where dateadd(day,n,startdate)<=enddate
order by n

Upvotes: 0

WmTR
WmTR

Reputation: 1

You can specify the dates in your WHERE clause. For example, WHERE date >=@STARTDATE AND date <=@ENDDATE. That should return the full date in your results.

Upvotes: 0

Related Questions