date comparison troubles.

[IBazz]

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.

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

[r937]

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

[IBazz]

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

[IBazz]

nope. wasn't that.

[IBazz]

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.

 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

[r937]

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

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

[IBazz]

rudy, thank you very much.

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

bazz

[r937]

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

[IBazz]

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.

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

[r937]

"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

[IBazz]

-- 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