MySQL date

TwiztedFake · Jan 2, 2006

On the site I'm working on I store the date in a MySql table every time a member submits a form. The default format for DATE in MySql is YYYY-MM-DD but I want it to display as MM-DD-YYYY when its called to be displayed.

Is there a way to do this in PHP or should I just change it from DATE to something like VARCHAR, which kind of screws up with ordering by date.

iNod · Jan 2, 2006

TwiztedFake said:
On the site I'm working on I store the date in a MySql table every time a member submits a form. The default format for DATE in MySql is YYYY-MM-DD but I want it to display as MM-DD-YYYY when its called to be displayed.

Is there a way to do this in PHP or should I just change it from DATE to something like VARCHAR, which kind of screws up with ordering by date.

Store it as YYYY-MM-DD than use explode to rearrange it for PHP processing..

iNod.

TwiztedFake · Jan 2, 2006

I just discovered that and am reading about it now.

eli03 · Jan 2, 2006

you can use strtotime( ) , see sample here

TwiztedFake · Jan 2, 2006

I used a function to convert the date for displaying it on pages.

PHP:

function dateconvert($date) {
		list($year, $month, $day) = split('[-.]', $date); 
		$date = "$day/$month/$year"; 
		return $date;
		}

Then simply used the following where I wanted to display it.

$date = dateconvert($row["date"]);
echo $date;

It works so thought I'd share it.

Kate · Jan 2, 2006

The date is stored in mySQL with format YYYY-MM-DD and that's normal behaviour.
You can use the mySQL date_format function in your query to display dates the way you want.

Example:

Code:

SELECT DATE_FORMAT(your_date_field, '%m-%d-%Y') as date_formatted
from your_table

This will display date like this: MM-DD-YYYY

mch · Jan 2, 2006

Why not use timestamp always and format it to whatever in PHP?

MySQL date

Established Member

Eating PieVIP Member

Established Member

VIP Member

Established Member

Domainosaurus RexTop Member

Established Member

zmla.com

uFurniture.com

Ambio.com

ACFA.com

Backpack.co

Infinity.TV

DineshKumar.com

Hearth.net

WolfAlpha.com

JMail.com

Similar threads

We're social

zmla.com

uFurniture.com

Ambio.com

ACFA.com

Backpack.co

Infinity.TV

DineshKumar.com

Hearth.net

WolfAlpha.com

JMail.com

Pinned

Appreciation

Preference

Appreciation

Upvoting

Agreement

Answers

Decisions

Relevance

Reaction

Facial expressions

Vocal expressions

Status

Workflow

Clarification

Feeling

Sympathy