best way to store date in database?

[mgworek]

right now, i am storing it as a text like 11/28/07

but now i am wanting to take that date and add 4 months onto it to display a future event that is not in the database yet.

I have mktime working to add 4 months onto todays date but i want to add it to a date in the database.

thanx!

[PHP_Chimp]

Although you can use the various date formats within the database, however my personal preference whenever I need to do any maths with dates is to use timestamps, as I find that they are the most flexible. As then you can add seconds, weeks, months, centuries...whatever. However it does mean more code...so there is a downside.
Have a look through the date [uk2.php.net] functions
date() = get your timestamp back into whatever format you want
time() = get your timestamp
strtotime() = do your arithmetic with the timestamp

Depending on your database you may well be able to do some/all of the arithmetic within the databse.

[Habtom]

I second that, timestamps is a good way of handling dates.

[mgworek]

I use a timestamp to get my date when i input it into the database but then i store it as a varchar. Should I change my fields to something else?

I use this code to get the date/time and insert it.
$date = date("m/d/y");
$time = date("H:i");

If what I am doing is ok how would i then pull it and format it so i can use with the following to add 4 months onto the month (I know how to get data from a table and everything, just not sure how to use it with mktime)?

$next = mktime(0,0,0,date("m")+4,date("d"),date("Y"));

thanx!

[PHP_Chimp]

varchar is ok, although you could also use numeric, as this would allow you to do more arithmetic using sql.

For adding any amount of time to your timestamp I find strtotime [uk2.php.net] the easiest way. As you can say -> strtotime(+1 month).

[coopster]

Personally, I prefer defining my date database columns using the DATE format or the TIMESTAMP format. Date and time manipulation is so much easier this way. Most databases have advanced DATE/TIME functions but there is always the standard SQL functions available, such as INTERVAL. You can add/subtract using the INTERVAL qualifiers from years down to seconds quite easily. You can do most, if not all, your date/time manipulation using the database engine as opposed to invoking PHP functions.

[mgworek]

Thank you everyone for the replies. I got it to work. The question I have now is is this the best way?

$last = $row1[Task_Date];
$next = explode("/",$last);
$future = strftime("%m/%d/%y",mktime(0,0,0,$next[1]+$row[Type_Months],$next[0],$next[2]));

It takes the date from the table ad adds the amount of months listed in the $row[Type_Months] field.