CODEWITHSUNDEEP
sqlsql-server-2008t-sqldate
user1613212
user1613212

Reputation: 93

Display dates from two fields

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:

  6/5/2012
  6/7/2012
  6/9/2012
  6/10/2012
  6/15/2012

Thanks in advance

Upvotes: 1

Views: 106

Answers (2)

Satish Pandey
Satish Pandey

Reputation: 1184

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;

Upvotes: 0

John Woo
John Woo

Reputation: 263723

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'

Upvotes: 1

Related Questions