Extracting Days from a date range

Question

I have here a date range from
"1/27/2014" - Start
"1/31/2014" - End

Note:

On Figure 1 - that is the data from SQL
On Figure 2 - that is how I want may data to be extracted

How can I do this using SQL query?
Is this possible?

possible duplicate of [Get a list of dates between two dates using a function](http://stackoverflow.com/questions/1378593/get-a-list-of-dates-between-two-dates-using-a-function) — Brett Schneider, Feb 04 '14 at 07:15

score 2 · Accepted Answer · answered Feb 04 '14 at 06:18

2

DECLARE @startDate DATE
DECLARE @endDate DATE
SET @startDate = '1/27/2014'
SET @endDate = '1/31/2014'
;WITH dates AS 
(
    SELECT @startdate as Date 
    UNION ALL
    SELECT DATEADD(d,1,[Date])
    FROM dates 
    WHERE DATE < @enddate
)
SELECT Date from dates

See it live

answered Feb 04 '14 at 06:18

vhadalgi

7,027
6
38
67

Hi, I have updates. Can you check if this is possible also. Thanks – user1647667 Feb 04 '14 at 06:29
hmm.. Update is incorrect ! 1/29/2014 should be girl.. and i dont think thers way to get in sql server? – vhadalgi Feb 04 '14 at 06:49
hi @vijaykumar can you check out on this. http://stackoverflow.com/questions/21569473/pivotting-date-range-using-sql – user1647667 Feb 05 '14 at 06:36
hi @vijaykumar. i have a question here. can I use this dates inside "IN" clause. EX: select * from table where date in (select dates from dates) – user1647667 Feb 07 '14 at 00:55
Yes! But you need to keep syntax correct!y `IN operator` – vhadalgi Feb 07 '14 at 02:05
what if im using it in pivot can i still use this? – user1647667 Feb 07 '14 at 02:26
LIKE THIS: count(splitdates) for PivotDate in (select date from dates) – user1647667 Feb 07 '14 at 02:26

Extracting Days from a date range

1 Answers1

See it live