Display dates from two fields

Question

I have a table with two columns startdate and enddate (of type DATETIME). When I pass two parameters that is start and end date, I need to display all the dates between the two dates from two columns.

i.e.. I have this table:

 startdate  enddate
 ---------------------
 6/1/2012   6/7/2012
 6/5/2012   6/9/2012
 6/10/2012  6/15/2012

When I pass two dates like 6/3/2012 and 6/20/2012, I want to display this result set:

Thanks in advance

What datatype are `startdate` and `enddate` ?? And which **version** of SQL Server are you using? — marc_s, Aug 21 '12 at 05:25
possible duplicate of [Calculate all dates between 2 dates in php?](http://stackoverflow.com/questions/11556354/calculate-all-dates-between-2-dates-in-php) — Thilo, Aug 21 '12 at 09:22

John Woo · Accepted Answer · 2012-08-21T05:10:21.937

1

UNION all dates in a SubQuery. Try,

SELECT allDates
FROM
(
    SELECT startdate as allDates
    FROM table
    UNION
    SELECT endDate as allDates
    FROM table
) a
WHERE allDates BETWEEN '6/3/2012' AND '6/20/2012'

edited Aug 21 '12 at 05:10

answered Aug 21 '12 at 05:04

John Woo

258,903
69
498
492

score 0 · Answer 2 · answered Aug 21 '12 at 05:17

MySql sample syntax :

CREATE TABLE TEST(STARTDATE DATE,ENDDATE DATE);

INSERT INTO TEST VALUES (NOW(), NOW());
INSERT INTO TEST VALUES ('2012-08-19', '2012-08-15');
INSERT INTO TEST VALUES ('2012-08-17', '2012-08-10');
INSERT INTO TEST VALUES ('2012-08-23', '2012-08-21');

SELECT STARTDATE FROM TEST WHERE STARTDATE BETWEEN '2012-08-15' AND '2012-08-23' 
UNION SELECT ENDDATE FROM TEST WHERE ENDDATE BETWEEN '2012-08-15' AND '2012-08-23' 
ORDER BY STARTDATE;

Display dates from two fields

2 Answers2