[SOLVED] php and mysql date

[ngreenwood6]

I want to store a date in my database. I want it stored as MM/DD/YYYY. I know there is a mysql date function but I believe it is supposed to be used as YYYY/MM/DD. Can someone please help me as to how to correctly store this.

[premiso]

Why not store it as a timestamp, then when it comes out of the db, use date and format it how you want to?

[gevans]

for mysql TIME() and DATETIME() functions check out this page

[ngreenwood6]

premiso can you help me with that? just a basic example would be great. i hate when people like gevans post stuff like look at this page. I already knew you could store the dates and times like that but that is not how I want them stored. I wish that if people didnt want to help they wouldnt post. thank premiso tho

[gevans]

Excuse me? You don't want to store in the standard formats that MySQL offer making it extremely easy to convert to and from any format you wish, and also making it a lot easier (and faster) to query the databse with regards to times and dates?

[Mchl]

If you read it carefully, you'd know it's the best way to store it.

 

You just need to know some extra functions, to convert dates stored in mysql format to your desired format.

These are

strtotime and date in PHP

There are also their analogues in MySQL

 

[premiso]

Basically it would require changing the table type to INT or datetime and using time to update the database.

 

If you want it stored in the DB as MM/DD/YYYY, which I do not know why you would, you would probably need a varchar of 10, but note that does limit your options, where as the timestamp can be pulled out and manipulated using the date function to show seconds, the month, the year, all of the above or some. I would highly suggest changing that table deal. But yea, my 2 cents on that part.

 

To store it as MM/DD/YYYY simply put the timestamp into date like so:

 

$dateFormat = date("m/d/Y");

$sql = "UPDATE tblname SET colDate = '" . $dateFormat . "' WHERE bob=1";

 

Should format the time correctly. I am not sure how to do it in mysql, so if that is your goal I would suggest reading up more on MySQL and Dates.

 

As for the rude comment back to gevans, I would agree with him, reading is your #1 key to finding a solution. He was basically suggesting different ways to manipulate dates in MySQL. I would suggest reading through that page as it will help you out in the long run.

[gevans]

premiso can you help me with that? just a basic example would be great. i hate when people like gevans post stuff like look at this page. I already knew you could store the dates and times like that but that is not how I want them stored. I wish that if people didnt want to help they wouldnt post. thank premiso tho

 

A lot of 'developers' don't use these resources and it can help you out a lot faster than writing into a forum, I'm not deterring from asking for help where it's needed, but a lot of information is readily available via the right resources such as mysql.com and php.net

[ngreenwood6]

premiso can you show me how to store it as the timestamp and then modify it when I pull it using the date function as that seems the better method.

[premiso]

I store my timestamps as int(11) but some people do not like to do that. So first modify your tables column to be int(11) (or timestamp whichever).

 

Then when you insert a time into the db, you insert the actual time stamp.

 

$sql = "INSERT into tbl_name (`timest`) VALUES ('" . time() . "')";

 

That will insert it as a timestamp.

[ngreenwood6]

Ok thanks for the help but the real problem I had was formatting it after it was stored. Can you help with that?

[Daniel0]

ngreenwood6, please do not report posts that are not violating any rules. Thanks.

[premiso]

$sql = "SELECT timest FROM tbl_name";

$result = mysql_query($sql);
while ($row =  mysql_fetch_assoc($result)) 
        echo date("m/d/Y", $row['timest']);

 

It's all about the date function.

[ngreenwood6]

Thank you for your time premiso. That was alot of help. I have got it now. I thought that was how but had a few things wrong.