[thelist] Date calculations in MySQL

[Chris Marsh]

On Wed, 21 Jan 2004 12:51:09 +0000, kris burford wrote
> >If I get today's date in a SQL statement in MySQL using Now(), how can I 
add
> >an integer number of days to give me a new date occurring x days from the
> >original?
> >
> >EG: INSERT INTO [table_name] ([date_field]) VALUES ([Now() + 30 days])
> 
> http://www.mysql.com/doc/en/Date_and_time_functions.html
> 
> this link was posted yesterday by Christopher Joseph - you're 
> probably wanting the Add_date function.
> 
> don't know whether you can have curdate() as the first expression, 
> have a play and see.

Many thanks for that. Another quick question:

Given the link that you posted, I can state:

INSERT INTO [table_name] ([date_field]) VALUES (ADDDATE(CURDATE(), 30 days])

However, what I now want to do is update a current record thus:

UPDATE [table_name] SET [date_field] = ADDDATE(CURDATE(), 30 days] WHERE uid 
= [uid]

This should work fine except for the fact that there may already be a date in 
this field. The logic is that if there is a date in the field that is AFTER 
today's date, then 30 days are added to the date already in the field. If the 
date in the field is today's date or previous, or the field value is NULL, 
then 30 days are added to today's date.

I think that the following works (correct me if I'm wrong):

UPDATE [table_name] SET [date_field] = ADDDATE((SELECT [date_field] FROM 
[table_name] WHERE uid = [uid]), 30 days] WHERE uid = [uid]

...but how do I get the conditional in there? Unfortunately I canno test this 
today as I have no access to the database, but I'm trying to get all my code 
written today for a smooth implementation tomorrow when I regain access to 
the database.

I am also going to be running this code through an ASP script - I don't know 
if this has any significance.

Many thanks in advance for your assistance.

Regards

Chris Marsh