Adding Days To Dates In MySQL

By  on  

One of my customers recently asked me to increment the date field on an event in their database by 14 days because their upcoming event got delayed. I knew the SQL statement would be simple and after doing some quick research, I found the magical SQL:

UPDATE events SET date_starts = DATE_ADD(date_starts,INTERVAL 14 DAY) WHERE event_id = 3;

This works on DATE and DATETIME field types. Note that it's "14 DAY" and not "14 DAYS".

Recent Features

  • By
    JavaScript Promise API

    While synchronous code is easier to follow and debug, async is generally better for performance and flexibility. Why "hold up the show" when you can trigger numerous requests at once and then handle them when each is ready?  Promises are becoming a big part of the JavaScript world...

  • By
    5 HTML5 APIs You Didn’t Know Existed

    When you say or read "HTML5", you half expect exotic dancers and unicorns to walk into the room to the tune of "I'm Sexy and I Know It."  Can you blame us though?  We watched the fundamental APIs stagnate for so long that a basic feature...

Incredible Demos

  • By
    Web Notifications API

    Every UI framework has the same set of widgets which have become almost essential to modern sites: modals, tooltips, button varieties, and notifications.  One problem I find is each site having their own widget colors, styles, and more -- users don't get a consistent experience.  Apparently the...

  • By
    Drag & Drop Elements to the Trash with MooTools 1.2

    Everyone loves dragging garbage files from their desktop into their trash can. There's a certain amount of irony in doing something on your computer that you also do in real life. It's also a quick way to get rid of things. That's...

Discussion

  1. Nice but I use the time() function, what is better ?

  2. @Catar: I don’t know which one is better, per say, but I know that this worked wonderfully!

  3. Felix

    it’s good!!!!…works fine and it’s usefull for me. tks!!

  4. Nice! Wouldn’t have occurred to me that MySQL had such a feature. Personally I prefer to do all my data arithmetic in seconds before handing the result over to an SQL query, but this is worth noting.

  5. Your blog is my Quick Bible in the Caos of Days

  6. red

    hi,

    i’m new to sql and i’m using mysql workbench to generate data mart. i need help..i want to generate dates (e.g. 2005-01-01), day of week ( 1 to 7), day of month (1 to 31), day name and month name for the whole of year 2005. the primary key is the date_key which starts from 1 to 365 and i already upload the date key into my database. it just that i need to generate all the above attributes using update/set but i dont know what is the correct sql script to generate them automatically and accordingly. would appreciate if u could help me on this..thanks

  7. Saleem Tahir

    Ok- That is helpfull but I want something like

    ” In a form an event is being added and it is add date is set to system date (today) Now I want the Event Expiry date to be automatically added in the event Expiry Date column based on the user choice. i.e User is give the choice to select that the event will expire in Two weeks, four weeks or Six weeks from the date of Adding”

  8. oSCho

    Thanks! I’ve been banging my head on the wall with this and your solution worked perfectly!

  9. AMIT GAJJAR

    THANKS ITS HELPFUL FOR ME…..

  10. Ben

    Brilliant! You save my day, David!

  11. More exciting that it could be:
    UPDATE events SET date_starts = DATE_ADD(date_starts,INTERVAL amountofdays DAY) WHERE event_id = 3;

    amountofdays is the name of the column in the same table;
    thus date_starts could be calculated depending on special amountofdays meaning for each row.

  12. Nazmul

    if (nice && useful)

    Print “Nice & useful post for me!”

    else

    print “Bad Post”

    After execution give me result :

    Nice & useful post for me! ;)

  13. Brian

    What if you wanted to add, say, 3 business days to a Thursday?

  14. james

    how can I add days?so that i can set the return days of borrowed books, this is a library system im making. pls help. thank you so much

  15. pduke

    …this was a life saver!!!!!
    thank you man!!
    +10

  16. how about adding month,is it MON or MONTH?

  17. Monika

    I want to add 30 Working Days to yesterdays date while Saturday and Sunday both are not working days.

  18. Jakub

    THANK YOU! I’ve been googling this for about 40 minutes (had to change 1864 records, way too much to do it manually) and your solution is the only one that worked for me :). And it’s almost 10 years old!

Wrap your code in <pre class="{language}"></pre> tags, link to a GitHub gist, JSFiddle fiddle, or CodePen pen to embed!