Date function not working in query

[PeggyBuckham]

I have a script that, along with other data, uploads a date into database. The date is generated using the date() function. 

date('m/d/y'.time());

 

What gets put into the data base is: 0.1098901098461

 

If I echo out the date like this

echo '<p>'.date('m/d/y'.time());.'</p>';

then I get the correct results: 10/8/13

 

Recently, after turning on php error messages on the server, I got an error (don't remember what it was exactly). I got rid of the error by putting this code in the header date_default_timezone_set('MST');

 

I've never had to set the default timezone before and I've never had problems with the date() function, but I know that someone out there can help me.

 

 

 

[B_CooperA]

What datatype are u using in your database? If you're using a timestamp, it saves the date in this format: date("Y-m-d G:i:s");

 

The best practice is to always save the date and time in format I mentioned above. When you're fetching it back, you can modify the output by converting the string to time like so: date("m/d/Y", strtotime($row['YOUR_DATE'])); 

Edited October 8, 2013 by B_CooperA

[Barand]

You should store dates in Y-m-d format. As you want the current date the easiest way is to use a TIMESTAMP type column then you can forget about it in your insert query as it will auto-update.

 

Alternatively you can put CURDATE() in your insert query and use DATE type column.

 

The reason you get the result you do is that your date wasn't in single quotes so it took it to be 10 / 8 / 13 (ten divided by eight divided by thirteeen)

Edited October 8, 2013 by Barand

[PeggyBuckham]

The reason you get the result you do is that your date wasn't in single quotes so it took it to be 10 / 8 / 13 (ten divided by eight divided by thirteeen)

 

lol ... Now that was a careless move wasn't it! Thanks so much!!

 

I also have another field in the database that has the timestamp in it. I always put a timestamp on almost everything. I just wanted a column with the date in a string so that my client could read it when the data was output onto a spreadsheet.

 

Thanks Again!

[Barand]

Format the date or timestamp when you extract from the table to the spreadsheet

SELECT DATE_FORMAT(mydatecol, '%m/%d/%y') as pretty_date FROM mytable