Delete by date, question

Databases
1

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.


delete from table
where end_date < CURDATE()

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?


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

or


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

bazz

2

I would use a different field, maybe a boolean, for that logic. That way you can keep the dates and logic intuitive.

3

Another possibility:

DELETE FROM table
WHERE end_date < CURDATE()
and end_date > '0000-00-00'

I don’t know which one is better.

4

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

you know what? it works on strings too!

i prefer the NULL solution

WHERE end_date < CURRENT_DATE

you’re welcome

:cool:

6

thanks rudy.

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

bazz

7

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

yes, that’s it exactly :slight_smile:

9

btw, did you notice how my solution didn’t mention NULL?

:smiley:

10

yup. I am changing my open-ended dates from ‘0000-00-00’ to NULL. :wink: