recurring dates in SQL statement

[txbakers]

Is there a mySql function that allows me to increment a date field by X days within the INSERT INTO statement?

for example:
on a calendar the event recurs for 4 days.

I can write the code to increment the day portion of the date, but would need to check for ene of month and reset accordingly. I know mySQL supports the DATE_ADD function, but does that work in the INSERT? When I tried it I received a javascript eror, probably due to the comma: DATE_ADD(eventdate, INTERVAL 1 DAY)

Is there an easier way to do this?

[amoore]

you mean like "INSERT INTO daytable VALUES ( DATE_ADD( CURRENT_DATE, INTERVAL 4 DAY) )"
or do you mean that you want it to do a whole bunch of inserts for you, each 4 days later?

I'm not sure why it was a javascript error if that's what you did.

[txbakers]

Yes. The code looks like this:

var numdays = Request.Form("numdays");
for (i = 1; i<= numdays; i++) {

var SQL = "insert into calevents values ( DATE_ADD( eventdate, INTERVAL 4 DAY) ) ";

execute query here...;

} // end for

Right now I just increment the date in JS instead of using a DATE_ADD function.

SO, if a person wants to list an event on Nov 3, which runs for 4 days, there will be four entires to the DB, each on successive days.

This part is easy - it's when the dates cross a month or year that it gets tricky.

[jatar_k]

If you use DATE_ADD it will take care of the crossing months business but you may have to use a different query.

I looked here and read the info on Date and Time Functions [mysql.com]. I couldn't find info about using it with insert but if you mess with it I imagine it can be done.

otherwise you can do
SELECT DATE_ADD("startdate", INTERVAL 4 DAY)
get the end date into a var and then throw it into your insert statement.

[txbakers]

I did figure it out. And it's sweeter than Pecan Pie.

var sql = "insert into calevents (event_name,description,category,schcode,location,edate) values ('" + evname + "','" + descn + "','band','" + Session("schoolcode") + "','" + locname + "',DATE_ADD(\'" + eventDate + "\',INTERVAL " + i + " DAY))" ;

I set this inside a for loop, incrementing "i" for either 1 day at a time, or 7 days at a time (5 days or every Friday for 5 weeks).

I needed to escape my single quotes around the eventDate parameter in the query above.

Sweet sweet this mySql.