1

I have an event in my mysqli database with is due to reoccur every year, i have set up a query which will create a new event exactly 1 year after the present event finishes.

I am wondering if i can set the new date not to be exactly 1 year from the current date but to the date of a specified day one year on?

for example my the date of my event could be Fri 6th Jan 16 but 1 year on the 6th Jan is a Saturday?

reason being my event is usually held on the first Friday of the year, so using the following query

$sql3 = 
mysqli_query($conn,"UPDATE temp_table SET
eventDate = DATE_ADD(eventDate , INTERVAL 1 YEAR)") 
or die(mysqli_error($conn,"sql3"));

like i said this will give me a date exactly 1 year to the day from the value of eventDate but it does not guarantee it will be a Friday, can this be modified to ensure it is the date of a Friday one year on?

Many thanks Luke

BBLJ84
  • 175
  • 1
  • 12

1 Answers1

0

Try this:

UPDATE temp_table SET eventDate =  
DATE_ADD(DATE_ADD(eventDate,INTERVAL 1 YEAR),
INTERVAL 5 - (DATE_FORMAT(DATE_ADD(eventDate, INTERVAL 1 YEAR),'%w')) DAY);

DATE_FORMAT(date, '%w') returns day of week. 0=Sunday..6=Saturday. 5 is friday.

So, this query adds 5-"'%w' of the next year date" days.

If "'%w' of the next year date" is 5, it adds 0 days, if 4, it adds 1 day, if 6, it adds -1 day etc.

Petr
  • 1,159
  • 10
  • 20