[RESOLVED] php/mysql date situation

[DKY]

My question is in two parts really. I have a database set up where I have the user enter a date in the following format mm/dd/yyyy. How can I change this in mysql to be stored as date format without messing up my whole database?

Second thing is, how do I get that to display like mm/dd/yyyy when I'm displaying the data?

[NogDog]

You can use the MySQL function STR_TO_DATE for inserting/updating values, and reverse it with the DATE_FORMAT function when selecting from the DB.

[solodesignz]

Or do it in some form like this:
I personally hate converting dates using MySql functions

function GetTimeStamp(){
	return date('YmdHis');
}

function ConvertDate($old_date, $layout = 'l, F j, Y'){

	if($old_date>0){
		//Remove non-numeric characters that might exist (e.g. hyphens and colons)
		$old_date = ereg_replace('[^0-9]', '', $old_date);

		//Extract the different elements that make up the date and time
		$_year = substr($old_date,0,4);
		$_month = substr($old_date,4,2);
		$_day = substr($old_date,6,2);
		$_hour = substr($old_date,8,2);
		$_minute = substr($old_date,10,2);
		$_second = substr($old_date,12,2);

		//Combine the date function with mktime to produce a user-friendly date & time
		$new_date = date($layout, mktime($_hour, $_minute, $_second, $_month, $_day, $_year));

		return $new_date;
	}


}


$newdate = GetTimeStamp(); //Store in DB

$data_string = ConvertDate($newdate, 'm/d/y'); // Convert timestamp from DB to display

[Installer]

Or:

function ConvertDate($old_date, $layout = 'l, F j, Y') { 
    return date($layout, strtotime($old_date));
}

But I'd go with the query functions.

[NogDog]

solodesignz wrote:

Or do it in some form like this:
I personally hate converting dates using MySql functions....

Seems like a lot of work to go through when you can just add one function call to your SQL query:

$insertSql = "INSERT INTO table (col1, col2, date) VALUES ('$val1', '$val2', STR_TO_DATE('$date', '%m/%d/%Y'))";

$selectSql = "SELECT col1, col2, DATE_FORMAT(date, '%m/%d/%Y') AS date_mdy FROM table";

But to each his own, I guess.

[solodesignz]

true, simple SQl statements its not that bad, but when you get large SQL statements, and nested sql querys... I just like to keep them as simple as possible... and when I can pull out a part of it to make PHP do it instead (to keep the SQL cleaner) i do.

But personal preference here =)

[DKY]

Okay, so I threw this into my while statement

mysql_fetch_assoc(mysql_query("SELECT DATE_FORMAT(SermonDate, '%m/%d/%Y') AS date_mdy FROM $uploadtable"));

and don't know how to extract the date from it. I tried

echo $date_mdy;

to no avail

[Piranha]

Interesting thread. Normally I would do as much as possible in the database, but in this case I think that I would use Installer's suggestion, use the date function to handle how the date should be viewed. The reason is that I think this issue is part of the page layout rather than a design issue in the programming. It would be much easier to show dates in different formats depending on where the person are connecting or language preference. And it would be much easier to later change the page and then maybe how the date is shown as well.

[DKY]

I've got it! I used SoloDesignz code and it helped out a bunch for displaying.

$old_date = $Row["SermonDate"];

		if($old_date>0){
        //Remove non-numeric characters that might exist (e.g. hyphens and colons) 
        $old_date = ereg_replace('[^0-9]', '', $old_date); 

        //Extract the different elements that make up the date and time 
        $_year = substr($old_date,2,2); 
        $_month = substr($old_date,4,2); 
        $_day = substr($old_date,6,2); 

        //Combine the date function with mktime to produce a user-friendly date & time 
        $new_date = $_month . "/" . $_day . "/" . $_year;
    	}
    	else
    	{
        	$new_date=" ";
    	}

[bradgrafelman]

Don't forget to mark this thread resolved (if it is).