[RESOLVED] Change Date Display

jkarr

Hello,

I can't find how to do this in any of my searches.

When a user creates a purchase order, the database records the date, and I want to display the date from the database.

The date is obviously stored as YYYY-MM-DD and I would like it to be MM-DD-YYYY. How do I retrieve the date and get it into this format?

Thanks,
Johnie Karr
Church of Christ
www.godsbible.org

rachel2004

You didnt' specify which database you are using, but if you are using MySQL, use the date in your queries. PHP has an equivalent command that is also named date . It's generally faster to allow the database to do the formatting for you if you are wondering whether to have the databaes do the formatting or have PHP do the formatting.

jkarr

Rachel,

I am using MySQL and PHP. I am using the date fieldtype and the now() function to include the date into the table when the row is inserted.

Now, I want to retrieve the date from the database and instead of it being displayed in the format YYYY/MM/DD I would like it to be displayed in the format MM/DD/YYYY and this is where I am don't know how to do.

Thanks again,
Johnie Karr
Church of Christ
www.godsbible.org

rachel2004

Look at the two links I gave you. Either will enable you to format as MM/DD/YYYY like you're trying to do.

tunage

Heres the function i use to change usa date to uk,
just pass it a string in YYYY-MM-DD format and itll
pass one back in DD-MM-YYYY format..

function mysql_date_to_uk($adate)
/* converts a mysql date to uk format string
(YYYY-MM-DD to DD-MM-YYYY) */
{
	$date_array = explode("-", $adate);
	$uk_date = ($date_array[2] . '-' . $date_array[1] .
'-' . $date_array[0]);
	return $uk_date;
}
//---------------------------------------------------------

rachel2004

With a query in MySQL, use the date_format() function. (I had mistakenly called it the date() function).

$sql = "SELECT DATE_FORMAT(myDateCol, '%M %d %Y') from TableName";

The PHP date() function is similar and well documented. It's just a matter of visiting the PHP manual using the link I had provided.

jkarr

Thanks everyone,

I'll try the date_format function and see if that works. If it does then I'll mark thread as resolved.

Thanks again,
Johnie Karr
Church of Christ
www.godsbible.org

jkarr

I'm using the date_format function as you described, but the echo is displaying nothing.

Here is my code. Will you please tell me if you see something wrong?

//Get the date display format correct
$date_get = mysql_query("SELECT DATE_FORMAT(date, '%M %d %Y')
 FROM purchase_orders WHERE random='$random' & company='$company3'")
 or die(mysql_error());
$date_got = mysql_fetch_array( $date_get );
$date_display = $date_got['date'];

I'm not getting any errors, just no results.

The echo line reads as follows:

<u><?echo $date_display; ?></u>

If I echo $date_get then I get

Function ID #15

or something like that. If I echo $date_got then I get

Array

If anyone has any ideas, please let me know, any help will be appreciated.

Thanks,
Johnie Karr
Church of Christ
www.godsbible.org

rachel2004

You have a few problems with your query statement. For one thing, the ampersand is not legal. Use the word 'AND' in it's place. I also believe the word 'date' is a reserved word in MySQL, but I don't recall exactly. Is that the name of the column in your table? Do you have backticks around it in the database? In my example, I've substituted the word with 'myColumn'. Here goes:

<?php 
// step 1: create query statement
$sql = "SELECT DATE_FORMAT(myColumn, '%M %d %Y') AS myDates
        FROM purchase_orders 
        WHERE random    =   '".$random."' 
        AND   company   =   '".$company3."'";

// step 2: execute query statement
$result = mysql_query($sql, $link)
    or die("<li />Could not execute query: <strong>".mysql_error()."</strong>
            <li />Here is the query: <strong>".$sql."</strong>");

// step 3: output results in any way you want formatted
echo "<font color='red'><em>Here are the dates:</em></font>
      <ul>";
while ($row = mysql_fetch_array($result)) {
    echo "<li />".$row['myDates'];
}
echo "</ul><em>finished!</em>";
?>

jkarr

Ok, it works now.

What I have done is added AS dates to the query and changed the & to AND. I also changed the fetch array to dates from date.

First is new php code. Second is Answers to your questions.

//Get the date display format correct 
$date_get = mysql_query("SELECT DATE_FORMAT(date, '%M %d %Y') AS dates
 FROM purchase_orders WHERE random='$random' & company='$company3'") 
 or die(mysql_error()); 
$date_got = mysql_fetch_array( $date_get ); 
$date_display = $date_got['dates'];

Answers

I also believe the word 'date' is a reserved word in MySQL, but I don't recall exactly. Is that the name of the column in your table?

Yes, date is the name of the column in my table.

Do you have backticks around it in the database?

I'm assuming you mean single quotes (ie ' ) but no, just the letters date just like that, no special formatting.

I hope my answers and problem have helped someone in the future.

Thanks for all your help rachel2004

Johnie Karr
Church of Christ
www.godsbible.org

rachel2004

Good to go. There are a few words that are reserved in MySQL (and I assume other RDBMS). In the case of MySQL, if you do choose to ignore the advice of not using reserved words as column names, you have to surround those reserved words with backticks (not the same as single quotes). The backtick key is located on the same key as the tilde symbol ( ~ ), at least on my keyboard.

Be sure you mark this thread resolved. Take care.