Formatting mySQL date in a dynamic field

[AdrianLe]

Hi all.. I've seen many articles on here about how to INSERT dates to the mySQL table, but I need to know how to format it into MM/DD/YYYY when the form dynamically populates from an existing record.  Just using a regular DW8 Recordset behavior for this...

Thanks-

[Lawrence_Cramer]

In ColdFusion you would use the date format function

#DateFormat(CreateODBCDate(yourDate), "dd/mm/yyyy")#

For more information on this do a Google search on: ColdFusion date format function

In PHP

date("Y/m/d")

For more info on this do a Google search on: php date format function

--
Lawrence   *Adobe Community Expert*
www.Cartweaver.com
Complete Shopping Cart Application for
Dreamweaver, available in ASP, PHP and CF
www.twitter.com/LawrenceCramer

[David_Powers]

AdrianLe wrote:

I need to know how to format it into MM/DD/YYYY when the form dynamically populates from an existing record.

Use the MySQL DATE_FORMAT() function and assign the result to an alias in your SQL.

For example, let's say you have a date stored in a field called start. The following would format the date in your desired format, and present it in the recordset results as start_date:

SELECT DATE_FORMAT(start, '%m/%d/%Y') AS start_date,
another_field, yet_another
FROM my_table

There's a full list of the formatting characters in the MySQL reference manual (follow the link above).

[David_Powers]

By the way, the answer to this question was in Formatting dates stored in MySQL in the Dreamweaver FAQ.

[AdrianLe]

Hello, yes.. But the date is only one of many fields being Selected in

the record...

[David_Powers]

That makes no difference. Just write your SQL so that it includes the DATE_FORMAT() and alias.

If you're being incredibly lazy, you can do this:

SELECT *, DATE_FORMAT(start) AS start_date
FROM my_table

[AdrianLe]

Interesting.. both of your suggestions return errors claiming that there are too few arguments, resulting in an empty query. I guess I'll have to dig deeper

[David_Powers]

Interesting.. both of your suggestions return errors claiming that there are too few arguments, resulting in an empty query.

Yes, there were too few arguments in the second example. It should be this:

SELECT *, DATE_FORMAT(start, '%m/%d/%Y') AS start_date
FROM my_table

Make sure you use single quotes around the '%m/%d/%Y'.

[AdrianLe]

Hello David.. yes, my actual code piece was almost identical.. I even changed it JUST so that it would retrieve the date field, and the query still ends up empty even though I have confirmed that field on that record has a value. Here is my select statement:

SELECT DATE_FORMAT(joindate, '%m/%d/%Y') AS newdate FROM member

[David_Powers]

That should work. What happens when you use SELECT joindate FROM member?

The alternative way to do it is to create a small PHP function like this:

function format_mysql($date) {
  $parts = explode('-', $date);
  if (is_array($parts) && count($parts == 3)) {
    return "$parts[1]/$parts[2]/$parts[0]";
  } else {
    return 'Date not available';
  }
}

Then display the result from your recordset like this:

<?php echo format_mysql($row_recordsetName['joindate']); ?>

[AdrianLe]

If I do just select joindate from member then I get the date: 1993-02-01 as you would expect.

I tried your PHP function and all that displayed are the empty hatch marks: //

[David_Powers]

There's something wrong with your code somewhere. I have just run the following test code, and it displays '02/01/1993', exactly as I expected it would.

<?php
function format_mysql($date) {
  $parts = explode('-', $date);
  if (is_array($parts) && count($parts == 3)) {
    return "$parts[1]/$parts[2]/$parts[0]";
  } else {
    return 'Date not available';
  }
}
$test = '1993-02-01';
echo format_mysql($test);
?>