Search Marketing

   
   

How To Use DATE_ADD in a Query

Fri May 02, 2008 9:55 pm
<<     >>
Comments: 0 Views: 2580

There are many reasons to use DATE_ADD in MySQL, perhaps when you are dealing with expirations or comparing the length of time of two different dates.

Using the following table data as an example you can see how to modify these dates.

mysql> select * from Name;


+----+--------+---------------------+
| ID | Name | Date |
+----+--------+---------------------+
| 1 | Joe | 2001-02-12 00:00:00 |
| 2 | Jack | 2003-03-15 00:00:00 |
| 3 | John | 1980-05-15 00:00:00 |
| 4 | James | 1994-09-21 00:00:00 |
| 5 | George | 1996-12-11 00:00:00 |
+----+--------+---------------------+

1. Add 1 year to all the dates.

mysql> select ID,Name,Date_ADD(Date,interval 1 year) as Date from Name;


+----+--------+---------------------+
| ID | Name | Date |
+----+--------+---------------------+
| 1 | Joe | 2002-02-12 00:00:00 |
| 2 | Jack | 2004-03-15 00:00:00 |
| 3 | John | 1981-05-15 00:00:00 |
| 4 | James | 1995-09-21 00:00:00 |
| 5 | George | 1997-12-11 00:00:00 |
+----+--------+---------------------+

2. Add 1 day to all the dates.

mysql> select ID,Name,Date_ADD(Date,interval 1 day) as Date from Name;
+----+--------+---------------------+
| ID | Name | Date |
+----+--------+---------------------+
| 1 | Joe | 2001-02-13 00:00:00 |
| 2 | Jack | 2003-03-16 00:00:00 |
| 3 | John | 1980-05-16 00:00:00 |
| 4 | James | 1994-09-22 00:00:00 |
| 5 | George | 1996-12-12 00:00:00 |
+----+--------+---------------------+

3. You can also use negative numbers after you set the interval.

mysql> select ID,Name,Date_ADD(Date,interval -11 hour) as Date from Name;
+----+--------+---------------------+
| ID | Name | Date |
+----+--------+---------------------+
| 1 | Joe | 2001-02-11 13:00:00 |
| 2 | Jack | 2003-03-14 13:00:00 |
| 3 | John | 1980-05-14 13:00:00 |
| 4 | James | 1994-09-20 13:00:00 |
| 5 | George | 1996-12-10 13:00:00 |
+----+--------+---------------------+

Note: Only dates less than 8000 years in the future are valid.



Comments
Name:
*
URL: http: (ex. cnn.com)
Comment:
*
Number:
Math (14 + 3)
* required
SubCategories
Books

© 2017 Christonium LLC

Christonium.com
|
Terms of Use
|
Privacy
ccc