What is so special about the SQL dateadd function? For those of you who know how daunting it is to add up dates and time, this function would be a life saver for you. This tutorial explains how the sql dateadd function is used while providing examples for better understanding.
Introduction
The SQL dateadd function is among a group of functions used to perform date arithmetic. It is popularly found in the MySQL and other database management systems.
Date objects are complex structures, seeing that a dates and time usually consist of several components including years, months, days, hours, minutes and seconds. Furthermore there is no round figure on how each component completes itself. For instance, a year has 376 days. A day has 24 hours. Hours and minutes each have 60 units. Seconds have a 1000 milliseconds and so on. So you find there is no consistency in the way you can add and subtract from these components.
Then again there is the matter of how the dates and times are displayed. For example midnight, or twelve AM can be written as 12:00 am or simply 00:00:00. This is quite a lot to keep track of and therefore the date SQL dateadd function comes in handy.
Syntax of the SQL Dateadd Function
For the rest of this article we are going to use the SQL dateadd function from MySQL. The syntax of this function comes in the format as shown below.
DATE_ADD(date,INTERVAL expression type)
MySQL also provides an alternative which happens to be a synonym for DATE_ADD(). The syntax for the synonym is as follows.
ADDDATE(date, INTERVAL expression type)
In both cases above the date can be a simple date format in the form of text or it can be a date and time value. The INTERVAL represents an integer that represents a number of the expression types. So if you plan to add a single day to the date my INTERVAL would be “1” and expression type would be DAY. Other expression types supported by MySQL are as follows.
SECOND
MINUTE
HOUR
DAY
MONTH
YEAR
MINUTE_SECOND “MINUTES:SECONDS”
HOUR_MINUTE “HOURS:MINUTES”
DAY_HOUR “DAYS HOURS”
YEAR_MONTH “YEARS-MONTHS”
HOUR_SECOND “HOURS:MINUTES:SECONDS”
DAY_MINUTE “DAYS HOURS:MINUTES”
DAY_SECOND “DAYS HOURS:MINUTES:SECONDS”
The Items in quotes represent the entries you can use as the INTERVAL.
Examples Showing How To Use DATE_ADD
Borrowing from the syntax above here are a few examples that show exactly how you would use your DATE_ADD function from within SQL statements.
To add one second to a particular date you can use:
SELECT DATE_ADD(“2009-12-31 23:59:59”, INTERVAL 1 SECOND);
The result would be:
2010-01-01 00:00:00
To add one day, one hour, one minute and one second, using the syntax above, the result would be:
SELECT DATE_ADD(“2009-12-30 22:58:59”, INTERVAL “1 1:1:1” DAY_SECOND);
This statement would produce this result:
2010-01-01 00:00:00
Following the same examples you can add days, months and years as well. Here is an example that adds a day to the date:
SELECT DATE_ADD(“2009-12-31 23:59:59”, INTERVAL 1 DAY);
The result is as follows:
2010-01-01 23:59:59
Conclusion
You can see clearly how the DATE_ADD functions automatically takes care of situations like when you have 59 seconds and you add one more second. The function automatically changes the seconds to zero and shifts the one to be added over to the minutes. The same happens across the board when deciding how to shift years, months, days and hours. Leap years are also factored in by this SQL dateadd function.
You can find other useful web development tips such as creating your own web polls here as well as tips on how to set up an online guest book .
