calculate date difference

I would go this way, to enhance performance:
Delete from Email
where PostDate < NOW() - 30


apart from that: is your field PostDate defined as datetime or as text field?

angelIII:

I am using MySql

I am having problem with the date getting inserted  then delete all emails more than 30 days.

Please, tell me what I am doing wrong I cannot insert the date to do the 30 days different

the field is called PostDate

            INSERT INTO `lulu50_ChurchesBulletin`.`Email` (
                  `UserID`,
                  `UserName`,
                  `From`,
                  `Subject`,
                  `PostDate`,
                  `Body`,
                  `EmailStatus`
                  )
                  VALUES (
                  '#session.AdsUserID#', '#cookie.MemUser#', '#Trim(FORM.TxtFrom)#',
                  '#Trim(FORM.TxtSubject)#', 'now()', '#Trim(FORM.input)#','i');

angelIII:

I am able to insert right now

I removed 'Now()'  to Now()

angelIII:

Delete from Email
where PostDate < NOW() - 30

it is deleting all my emails

PostDate is defined as date

I set the date value to my record to be 2011-12-29

but I am still seeing the record.

the type for PostDate is Date

How DATE_SUB(NOW() display the date?

my field display the date like this 2011-12-29

2011-12-29 is in the future, not in the past.
I understand you want to delete data older than 30 days?

yes I do want it to be in the future


ok, please help me explain:

a user comes to my site sends an email I take the date when the user sends the email calculated to what every the current date and if the difference more than 30 days I want to delete their email.

not clearer to me ...

let's speak with data samples:
it's today 2011-10-28.
I am your customer, I want to send an email with your site.
the email is stored with today's date?

so, 30 days later (2011-11-27), you want this email to be deleted, right?
so, you have to consider yourself to be on that date, so there you want to delete emails that are 30 days old, aka created 30 days before "now()"

so, my suggestion should work.

I got it to work !!!!!


Thank you

Thank you