CODEWITHSUNDEEP
mysqldateselect
user1492669
user1492669

Reputation: 41

MySQL Select where column greater than or equal to closest past date from given date

TABLE

Table:

Id             Date
1            01-10-15
2            01-01-16
3            01-03-16
4            01-06-16
5            01-08-16

Given two dates startdate 01-02-16 and enddate 01-05-16. I need to get the data from the table such that it returns all data between the closest past date from startdate and closest future date from enddate including the two dates. So the result will look like this.

Result:

Id             Date
2            01-01-16
3            01-03-16
4            01-06-16

What I am doing

What I am doing now is fetching the whole data and removing from the array results less than closest fromdate and greater than closest enddate

What I want

What I want is to do this in query itself so that I don't have to fetch the whole data from table each time.

Upvotes: 1

Views: 3801

Answers (4)

jonju
jonju

Reputation: 2736

Try this 

Select *
From 
    dTable 
Where 
     [Date] 
Between 
       (Select 
              Max(t1.Date) 
        From 
            dTable t1 
        Where 
             t1.date <startdate) And    
       (Select 
              Min(t2.Date) 
        From 
             dTable t2 
        Where 
             t2.date >enddate)

Upvotes: 1

Blank
Blank

Reputation: 12378

If you column's type is date, use union can do it:

(select * from yourtable where `date` <= '2016-01-02' order by `date` desc limit 1)
-- This query will get record which is closest past date from startdate
union
(select * from yourtable where `date` => '2016-01-05' order by `date` asc limit 1)
-- This query will get record which is closest future date from enddate
union
(select * from yourtable where `date` between '2016-01-02' and '2016-01-05')

Demo Here

Upvotes: 2

Yong
Yong

Reputation: 1127

If Date is String, STR_TO_DATE and DATEDIFF can be used here.

SELECT  id,  Date
FROM tab
where
STR_TO_DATE(Date, '%d-%m-%y') BETWEEN('2016-02-01')AND('2016-05-01')
or 
id = (SELECT id FROM tab
where STR_TO_DATE(Date, '%d-%m-%y') > '2016-05-01'
ORDER BY DATEDIFF(STR_TO_DATE(Date, '%d-%m-%y'), '2016-05-01') Limit 1)
or 
id = (SELECT id FROM tab
where STR_TO_DATE(Date, '%d-%m-%y') < '2016-02-01'
ORDER BY DATEDIFF('2016-02-01', STR_TO_DATE(Date, '%d-%m-%y')) Limit 1)

Upvotes: 0

SIDU
SIDU

Reputation: 2278

Imaging your date is in YYYY-mm-dd

## get rows within the dates
SELECT * FROM tab WHERE ymd BETWEEN :start_date AND :end_date
## get one row closest to start date

UNION
SELECT * FROM tab WHERE ymd < :start_date ORDER BY ymd DESC LIMIT 1
## get one row closest to end date

UNION
SELECT * FROM tab WHERE ymd > :end_date   ORDER BY ymd      LIMIT 1

Upvotes: 2

Related Questions