1

Below is my data and query,

academic year start date - 2018-01-01
academic year end date   - 2018-06-30

Term table

term_id    parent_id      start_date          end_date 
1          null                2018-01-01          2018-01-30
2          1                   2018-01-01          2018-01-10
3          1                   2018-01-11          2018-01-20
4          null                2018-02-01          2018-02-28
5          4                   2018-02-01          2018-02-10
6          4                   2018-02-11          2018-02-20

I want to add new term which should not be in date range of term_id 1,2 and also in date range of academic year start and end date. But in my query it is not working below is my query,

I have entered, it should not entered from below query

start_date - 2018-02-11 
end_date   - 2018-02-25 

SELECT * from term
where parent_id=null
and start_date >= 2018-02-11 
and end_date <= 2018-02-25
and start_date >= 2018-01-01(academic year start date)
and end_date <= 2018-06-30(academic year end date)

Same for enter sub term it should be in parent term date range and unique in all sub term of same parent term. I have entered, which should not enter,

start_date - 2018-02-13
end_date -2018-02-18

My query is below,

SELECT * from term
where parent_id=4
and start_date >= 2018-02-13
and end_date <= 2018-02-18
and start_date >= 2018-02-01(parent term start date)
and end_date <= 2018-02-28(parent term end_date)
and start_date >= 2018-01-01(academic year start date)
and end_date <= 2018-06-30(academic year end date)
hetal gohel
  • 335
  • 9
  • 21
  • Is all that code what you're really using? as are the values? – Funk Forty Niner Apr 04 '18 at 18:01
  • I love it when they're present, they edit... put fluff back in and don't bother commenting here. Are you the type that won't bother and just want to be spoonfed? I might have something you might bark back at me with. – Funk Forty Niner Apr 04 '18 at 18:07
  • It seems like the marked duplicate answer is not matching this question. Hetal is trying to find the records by comparing date range and she is not getting expected data. Hetal please correct me if i am wrong. – Gopal Joshi Apr 05 '18 at 05:20
  • yes ,I am agree with @GopalJoshi – hetal gohel Apr 05 '18 at 06:54
  • @hetalgohel - Is your column name start_time like your above example, or start_date like your code says? – Adam J Apr 05 '18 at 13:27

1 Answers1

0

Ok, so I'm going to ask this: Is your column name start_date or start_time?

LEAVING BELOW BECAUSE STILL RELEVANT

Dates are used in SQL statements like strings, so you have to wrap them in double quotes ". So, you're query should be something like this:

SELECT * from term
where parent_id=4
and start_date >= "2018-02-13"
and end_date <= "2018-02-18"
and start_date >= "2018-02-01"
and end_date <= "2018-02-28"
and start_date >= "2018-01-01"
and end_date <= "2018-06-30"

However, your SQL statement is very convoluted. You're saying where the start_date is greater than February 13, AND it's greater than February 1, AND it's greater than January 1, and the same for the end date.

What you could do is simply say:

SELECT * FROM term
WHERE parent_id = 4
AND start_date >= "2018-01-01"
AND end_date <= "2018-06-30"

That would accomplish all of the above "AND" statements.

Adam J
  • 506
  • 3
  • 17
  • The only quibble I have is that it's not "All dates are treated as strings" but rather "strings used in comparisons with dates are converted to dates", though even that is a bit oversimplified. – Sammitch Apr 04 '18 at 18:05
  • You're correct, I could probably have phrased it a bit better. I'll edit. – Adam J Apr 04 '18 at 18:06
  • Oh, also those all have to be *single* quotes. – Sammitch Apr 04 '18 at 18:17
  • @Sammitch - Only depending on what you wrap your statement in. If you wrap it in double quotes, yes. – Adam J Apr 04 '18 at 18:21
  • thanks for your help ,but this answer is not worked from me,I think I have done mistake in check date range .Please read my question. – hetal gohel Apr 05 '18 at 04:10
  • @webdevsoup please read the question carefully and hetal added dates in the query here just for explanation. single and double quota is not here issue. – Gopal Joshi Apr 05 '18 at 05:23
  • @hetalgohel - It's hard to understand exactly what your issue is. The broken English makes it extremely difficult. GopalJoshi - I read the question SEVERAL times before I answered it trying to understand exactly what they were asking. Answered it to the best of my understanding for what he's looking for. – Adam J Apr 05 '18 at 13:22