SitePoint Sponsor

User Tag List

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

    delete by date, question

    I have a column called 'period_end', where its values may include '0000-00-00' if the period is open-ended.

    So far in another instance, I have been used a delete statement like this.

    Code MySQL:

    But that would include the deletion of '0000-00-00'.

    Q1. should the open-ended value (for a period that is allowed to run until specifically set), should be 'NULL'?

    Q2. I wonder I should avoid that 'numeric' comparison and to do it like one of these ways?

    Code MySQL:
    delete from table 
    where end_date between( '0000-00-01' and (CURDATE(), interval - 1 day)

    or

    Code MySQL:
    delete from table
    where end_date < ADDDATE(NOW(), INTERVAL -24HOUR);


    bazz

  2. #2
    SitePoint Evangelist speda1's Avatar
    Join Date
    Jan 2002
    Posts
    550
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would use a different field, maybe a boolean, for that logic. That way you can keep the dates and logic intuitive.

  3. #3
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Another possibility:
    Code:
    DELETE FROM table
    WHERE end_date < CURDATE()
    and end_date > '0000-00-00'
    I don't know which one is better.

  4. #4
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you both

    @guido. you have resolved my main concern, which was that I suddnely got a notion that < is meant for numeric comparisons rather than date related ones.

    I shall continue to use < for this.

    bazz

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by IBazz View Post
    I suddnely got a notion that < is meant for numeric comparisons rather than date related ones.
    you know what? it works on strings too!

    i prefer the NULL solution
    Code:
    WHERE end_date < CURRENT_DATE
    you're welcome

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

  6. #6
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks rudy.

    I'm off now to find out about curdate() and CURRENT_DATE. maybe they're interchangeable but maybe not.

    bazz

  7. #7
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oh, I forgot; CURRENT_DATE is a synonym for curdate().

    But, is CURRENT_DATE standard SQL and curdate() not? I prefer to use standard SQL in case I use a different db someday.

    bazz

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by IBazz View Post
    But, is CURRENT_DATE standard SQL and curdate() not?
    yes, that's it exactly
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    btw, did you notice how my solution didn't mention NULL?

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

  10. #10
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yup. I am changing my open-ended dates from '0000-00-00' to NULL.


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
  •