SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,249
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    where the start date is "up to 9 months from today" forwards or backwards in time?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    nope. wasn't that.

  5. #5
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,249
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by IBazz View Post
    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.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    rudy, thank you very much.

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

    bazz

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,249
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    the part i gave you or the part you changed? i am once again comfusled
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,249
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    "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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    -- 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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •