View Full Version : Modifying Date output

12-13-2008, 06:21 PM
This should be an easy one for the experts...

I want to modify a date i am echoing from my sql database

This is what is stored in my sql database
2008-10-13 13:32:40

I would like to echo it like this

I've never done it so any help is appreciated. Thanks in advance.

12-13-2008, 06:25 PM
You can specify this in your query. Check out MySQL's DATE_FORMAT (http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format) function

12-13-2008, 06:35 PM
Also - http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date

12-13-2008, 07:04 PM
so does this mean now i have to make a separate query just to concat the date?

why can't i do something like this... (this doesn't work though)

date("F/j/Y",$row['job_create_date']) \\job_create_date = 2008/11/02 4:30:20;

12-13-2008, 09:10 PM
No, you shouldn't need a separate query to pull a formatted date.

date("F/j/Y",$row['job_create_date']) \\job_create_date = 2008/11/02 4:30:20;
That doesn't work because the date() (http://us.php.net/manual/en/function.date.php) function expects a timestamp as the second argument.

If you want to go that route, you would first need to convert your $row['job_create_date'] using strtotime() (http://us.php.net/manual/en/function.strtotime.php). However, it would be more efficient to let MySQL pull the formatted date, rather that pulling and unformatted date and formatting it in php.

12-13-2008, 10:38 PM
how do you add that to my original query? I'm not sure how to do it.

SELECT * FROM `job_detail` WHERE (`job_cat2` = '$id' && `job_ended` != '1')"

12-13-2008, 11:10 PM
If you just need the date portion without changing it's format:

SELECT *, DATE(`job_create_date`) AS just_date FROM `job_detail` WHERE (`job_cat2` = '$id' && `job_ended` != '1');

Your date will be accessible via $row['just_date'] (based on your previous example)

If you'd like to change the format of the date (to say Nov 2, 2008):

SELECT *, DATE_FORMAT(`job_create_date`, '%b %e, %Y') AS formatted_date FROM `job_detail` WHERE (`job_cat2` = '$id' && `job_ended` != '1');

Your date will be accessible via $row['formatted_date'] (based on your previous example)