Adding Days to Date

TheTrain1868

Hello everyone. I am trying to add days to a date that I'm getting out of my database.

The value in the database that is stored is a VarChar(500) and looks like "01/01/2007" for example.

How do I take that date and add days (5 for example) to it and then print it out?

I've looked at the DATE_ADD function but I can't seem to get it to work.

Thanks in advance.

cgraz

There are two problems w/your field.

Why are you using a varchar to store a date? MySQL has a date field - if you use it, you'll be able to use the built-in date functions.
Even at a varchar, why varchar 500 instead of varchar 10?

Use a date field then try DATE_ADD() again.

NogDog

I strongly concur with cgraz that you should use a date field to store a date value, and thus make the associated date functions easier to use. But if for some reason you cannot do so, you could use the STR_TO_DATE() function to convert the column's value into a date type, then use that in the DATE_ADD() function:

DATE_ADD(STR_TO_DATE(`date_column`, '%m/%d/%Y'), INTERVAL 5 DAY)

TheTrain1868

What is the correct format of the date for the database?

TheTrain1868

I just tried your solution NogDog and got this:

Parse error: parse error, unexpected T_LNUMBER in C:\Inetpub\Home\forms\view_classes.php on line 299

TheTrain1868

Ok I changed the variable type in the database to be "Date" but when I try it, it still doesn't work.

cgraz

Could you provide more info as to what's not working?

Have you checked that there is data in your table, particularly in the date fields.
What does your query look like?
Are you running your query via PHP. Have you checked for any errors returned from MySQL?

If you've done all that and still having trouble, post your code.

NogDog

TheTrain1868 wrote:
I just tried your solution NogDog and got this:

Parse error: parse error, unexpected T_LNUMBER in C:\Inetpub\Home\forms\view_classes.php on line 299

You do realize that that was just a sample snippet of a SQL statement, not PHP code?

TheTrain1868

Here is the code I'm using:

$query = "SELECT title, date_of_hire FROM aci_employees WHERE username = '$emp'";
$result = mysql_query($query);

if($result)
{
while($key = mysql_fetch_array($result))
{
print "<tr>";

	print "<td class='timeSheetDay' colspan='8'>";

	print "Date of Hire: " . $key['date_of_hire'];

	$newDateOfHire = DATE_ADD($key['date_of_hire'], INTERVAL 5 DAY);

	print "<br/>Updated Date of Hire: " . $newDateOfHire;

	print "</td>";
}

}

Hopefully this helps.

cgraz

DATE_ADD() is a mysql function - it needs to go in your query so mysql can execute it. It will add the dates on the fly, and return the result so you can display using PHP.

TheTrain1868

How would you go about doing that?

cgraz

from the manual

mysql> SELECT DATE_ADD('1997-12-31 23:59:59',
-> INTERVAL 1 DAY);
-> '1998-01-01 23:59:59'

select date_add(date_of_hire, interval 5 day) as dateField from aci_employees