CODEWITHSUNDEEP
mysqlsql
Ganesh M
Ganesh M

Reputation: 63

between in two columns not working properly

I have one table events

using this table i have to get the records between two dates and two columns in mysql,see below i mentioned my table format

eventId          startDate            dueDate

 1                2018-01-15          2018-01-17  
 2                2018-01-25          2018-01-25
 3                2018-01-11          2018-01-16

Suppose i want to take the records from 2018-01-14 into 2018-01-16 means actual results should come like below

My Expected answer

eventId
 1
 3

I tried like this but nothing is coming

 SELECT eventId FROM events WHERE startDate <= '2018-01-30' AND DueDate <= '2018-01-30'

I am getting output

eventId
 3

Upvotes: 0

Views: 49

Answers (2)

knuti
knuti

Reputation: 9

you mean you want all events that 'are active' in a given date-range, e.g. between 2018-01-14 and 2018-01-16?

it helps to precisely formulate the 'what are my criteria' in natural language. i re-formulate what i think you mean:

give me all events that

  • start before (or on) 2018-01-16
  • end after (or on) 2018-01-14

it looks a bit weird - you have to compare event.startDate against range.endDate (and vice versa). but that is the correct check you need. now it is easy to translate that to SQL:

SELECT eventId 
FROM events
WHERE startDate <= '2018-01-16'
  AND dueDate <= '2018-01-14'

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

You could use between the range you need eg:

 SELECT eventId 
 FROM events 
 WHERE startDate  BETWEEN  '2018-01-14' AND  '2018-01-16'
 AND DueDate BETWEEN  '2018-01-14' AND  '2018-01-16'

or you can also use different range for the two columns

for get the row with id = 3 you need 2018-01-11 and not 2018-01-14 for start range

 SELECT eventId 
 FROM events 
 WHERE startDate  BETWEEN  '2018-01-11' AND  '2018-01-16'
 AND DueDate BETWEEN  '2018-01-11' AND  '2018-01-16'

or you could use an OR Condition for check just one of the two column from 14 to 16

 SELECT eventId 
 FROM events 
 WHERE startDate  BETWEEN  '2018-01-14' AND  '2018-01-16'
 OR  DueDate BETWEEN  '2018-01-14' AND  '2018-01-16'

Upvotes: 1

Related Questions