date comparison troubles.

My query works fine but for the last part as below. I need to retrieve records where the start date <= today and end date is >= today.

I also need the records where the strat date is up to 9 months from today.

This part of the query ensures it returns nothing when there is data to be returned. I have had little success from searching and reading the docs.

Code MySQL:

---

WHERE ( pl.start_date <= CURDATE() or pl.start_date <= date_sub(curdate(), interval 270 day) )
    AND pl.end_date >= CURDATE()

---

Please give me a nudge.

bazz

where the start date is "up to 9 months from today" forwards or backwards in time?

forwards.

I want to show start dates prior to today where end date is after today
I want to show where start date is after today but no further after today than 270 days.

Penny might have dropped.... I might need a + to be added to my query.

I'll try that and post back.

bazz

nope. wasn't that.

OK, after more thought; the first part of the query works before the OR statement. BUT I should be able to use this more efficiently by just asking for the start date to be earleir than today+270 and where end date is after today.

Code MySQL:

---

 WHERE pl.start_date <= date_sub(current_date, interval + 270 day) 
    AND pl.end_date >= CURDATE()

---

The first query brings some results whereas this one returns none. But this seems more like what I need.

bazz

Quote:

---

Originally Posted by IBazz

I need to retrieve records where the start date <= today and end date is >= today.

I also need the records where the strat date is up to 9 months from today.z

---

these are two separate conditions, and you shouldn't comingle them with the start date

Code:

---

WHERE
 /* first, let's do this one:
    start date <= today and end date is >= today */
      CURRENT_DATE BETWEEN pl.start_date
                      AND pl.end_date
  OR
 /* second, let's do this one:
    start date is up to 9 months from today */
      pl.start_date BETWEEN CURRENT_DATE
                        AND CURRENT_DATE + INTERVAL 9 MONTH

---

rudy, thank you very much.

I had to change another bit of the query but it works fine now. :cool:

bazz

the part i gave you or the part you changed? i am once again comfusled

oh dear, I seem to do that too often!!

The part you gave me works perfectly. The other bit I had to change was to move two AND clauses up to the joining condition rather than showing them after the WHERE. If I were to leave them after the WHERE, they seem to be ignored.

Code:

---

SELECT
      ps.product_name
    , ps.room_type
    , pl.start_date
    , pl.end_date
    , tb.tariffs
  FROM product_live AS pl
  INNER
  JOIN products_stock AS ps
    ON ps.id = pl.product_id
  inner
  JOIN ( SELECT live_product_id
              , GROUP_CONCAT(
                      CONCAT_WS(','
                          , age_group
                          , t.tariff
                          , t.tariff_terms_abbr
                          , t.currency
                          , tt.tariff_terms) order by t.tariff asc
                  SEPARATOR ';' ) AS tariffs
          FROM tariffs as t
          inner join tariff_terms as tt
          on t.tariff_terms_abbr = tt.tariff_terms_abbr
        GROUP
            BY live_product_id ) AS tb
    ON tb.live_product_id = pl.id
    and pl.business_id =?
    AND pl.priority = 'b'
    WHERE
 /* first, let's do this one:
    start date <= today and end date is >= today */
      CURRENT_DATE BETWEEN pl.start_date
                      AND pl.end_date
  OR
 /* second, let's do this one:
    start date is up to 9 months from today */
      pl.start_date BETWEEN CURRENT_DATE
                        AND CURRENT_DATE + INTERVAL 9 MONTH

---

bazz

"seem to be ignored" simply means you did not combine your ANDs and ORs properly ;)

however, putting those conditions into the JOIN also works -- be careful doing this with outer joins, though

:)

Quote:

---

Originally Posted by r937

-- be careful doing this with outer joins, though

:)

---

OK. I'll look at it again after some sleep. Maybe I should have used brackets around the ANDs and ORS when putting those conditionals after the where.

4:22am here so time for zzzzz

thanks again.

bazz