Date

[amarafzal]

Hi
At the moment when i pull out the date field from the MYsql database, it comes out in the following format:
2002-03-04

What do i need to do if i want it to be displayed like eg:
March 4 or
Mar 4 2002

What code do i need any help please?

thanks in advance people

[EricC]

MySQL has a feature called DATE_FORMAT you can use in your select statement. (Scroll down towards the bottom there are a lot of different formatting options you may want to use)

SELECT DATE_FORMAT(tblDatelabel, '%M %D %Y') FROM tblName where fieldID = 'Something'

Good luck. 🙂

[amarafzal]

Ive tried to incoporate the code you gave me but cant get it to work:

$sql = "SELECT job_id, contact_name, contact_telephone, date_posted, job_title, job_summary, person, package, location, salary FROM $table_name";
$result = @($sql,$connection) or die(mysql_error());

while ($row = mysql_fetch_array($result)) {
$job_id = $row['job_id'];
$contact_name = stripslashes($row['contact_name']);
$contact_phone = $row['contact_telephone'];
$date_posted = $row['date_posted'];
$job_title = stripslashes($row['job_title']);
$job_summary = stripslashes($row['job_summary']);
$short_summary = substr($job_summary,0,150);
$location = stripslashes($row['location']);
$salary = $row['salary'];

$date ="SELECT DATE_FORMAT(date_posted, '%M %D %Y') FROM $table_name where date_posted = '$date_posted'";

[scoppc]

You can create a function to handle this, for example:

function dateconverter($date){
//assume that the $date is in YYYY-MM-DD  format
$exp=explode("-",$date);
$year=$exp[0];
$month=$exp[1];
$date=$exp[2];
//create a UNIX timestamp
$timestamp=mktime(0,0,0,$year,$month,$date);
//you can display in any format you like using date()
print date("d M Y",$timestamp);
//this will give you 04 Mar 2003
}

HTH

Note: please double-check the code, I'm not sure esp on explode functions. hehehe

[BuzzLY]

I think this is what he meant:

$sql = "SELECT job_id, contact_name, contact_telephone, DATE_FORMAT(date_posted, '%M %D %Y'), job_title, job_summary, person, package, location, salary FROM $table_name";

[EricC]

amarafzal, the code I posted was just an example of the syntax of using date_format in a MySQL select statement. The code that BuzzLY posted is exactly how you could format your query. 🙂

[amarafzal]

Hi
thanks for the tips ALL, but nothing happend, the field on the web page goes blank. I used the following that BUZZLY gave me:

$sql = "SELECT job_id, contact_name, contact_telephone, DATE_FORMAT(date_posted, '%M %D %Y'), job_title, job_summary, person, package, location, salary FROM $table_name";

any ideas?

[EricC]

Is the date_posted field in your database stored as a timestamp or as something else? If its not stored as a timestamp then DATE_FORMAT isn't going to necessarily work for you. You might want to look at incorporating the function scoppc posted to manipulate the value coming out of that field into your desired format. Check the PHP manual for the formatting options of the date .

[paul088]

Maybe you should change the "type" of the field for this entry in your database. It is probably set to "Date" which normally takes the format of YYYY-mm-dd. Try something like "varchar".