Changing Date Format

[skillednerd]

I have a db with db with date and the format for the date is 1975-12-22 and I want them to appear as 1975-DEC-22

I don't mind changing the date from date to varchar to make this happen maybe pulling each record and using find and replace or something. What would be the best way to make this happen.

 

thanks in advance.

[Pikachu2000]

Do not change the date to VARCHAR. All you need to do is use MySQL's DATE_FORMAT() function when you run the query to retrieve the data for display.

 

SELECT DATE_FORMAT(`date_field`, '%Y-%b-%d') as `f_date` FROM `table`

 

If the month has to be upper case, you can wrap the whole DATE_FORMAT() in UPPER() like this

SELECT UPPER(DATE_FORMAT(`date_field`, '%Y-%b-%d')) as `f_date` FROM `table`

 

In either case, the formatted date will be in the array index with the name of the alias, in this case 'f_date' if you use an associative array when you fetch the data (such as $row['f_date']).

[skillednerd]

Thank you very much!

[skillednerd]

Cant actually get that to work.

 

 mysql_query( "SELECT UPPER(DATE_FORMAT(`birth`, '%Y-%b-%d')) as `f_date` FROM `154k_USA_profiles`" );
    $ybirth = $row['f_date'];
    

[raj23]

Hi,

 

  What you do is change the way that you display the date returned from the database.

This is easily achieved using the MySQL DATE_FORMAT function.

Using date_format you can set any date format:

    select date_format(FromDate,"%d/%m/%Y") AS FormattedDate FROM tbl_event

[PFMaBiSmAd]

Cant actually get that to work.

 

You code is missing the code it would need to work. You are not assigning the value that mysql_query() returns into a variable. It would be a result resource if the query executes without any errors. You are also not fetching a row from that result resource, so the $row variable doesn't exist.