Adding months to a date

[jsteiner87]

I have a date being stored in a MYSQL database. I want to take that day and add months to it. How do you go about doing this? It is stored as a Date (MM-DD-YYYY) in the database.

 

[sleipnir214]

Are you using a MySQL column of type date? MySQL stores values in that column type as YYYY-MM-DD, not MM-DD-YYYY.


Want the best answers? Ask the best questions! TANSTAAFL!

 

[jsteiner87]

Sorry about that, it is stored as YYYY-MM-DD. I have no idea what I was thinking.

 

[sleipnir214]

What version of MySQL are you running?



Want the best answers? Ask the best questions! TANSTAAFL!

 

[sleipnir214]

I don't guess the version matters.

Given a table foo which contains:

[tt]+----+---------------------+
| Id | the_stamp |
+----+---------------------+
| 1 | 2006-01-30 10:15:00 |
| 2 | 2006-02-28 10:30:00 |
| 3 | 2006-03-15 10:45:00 |
+----+---------------------+
[/tt]

The query:

select date_add(the_stamp, interval 1 MONTH) as new_date from foo;

will return:

[tt]+---------------------+
| new_date |
+---------------------+
| 2006-02-28 10:15:00 |
| 2006-03-28 10:30:00 |
| 2006-04-15 10:45:00 |
+---------------------+
[/tt]


Want the best answers? Ask the best questions! TANSTAAFL!

 

[jsteiner87]

Is there a way to do it with PHP. I have a variable that contains the amount of months that I would like to add to the date. The amount of month can change.

 

[sleipnir214]

Trust me, you want to do this on the MySQL side. It handles data-math more easily than PHP.

Programmatically generate the query from PHP:

If $number_of_months contains the number of months to add, then:

$query = "select date_add(the_stamp, interval " . $number_of_months . " MONTH) as new_date from foo";

Will produce whatever query you need. You script can pass this query to MySQL and fetch the return.




Want the best answers? Ask the best questions! TANSTAAFL!

 

[jpadie]

I agree with sleipnir214 on using mysql for d/t manipulations.

if you must do it in php then you could try this (i have not tested this in <PHP5):

<?
$olddate = "2006-06-01";
$newdate = date("Y-m-d", strtotime("+1 month", strtotime($olddate)));
echo $newdate;
?>