Date display format

[erickia]

I know how to change the way the date varible displays but how can I change the way a recordset date field displays? I would like it to look like "May 16th 2005."

[David Powers]

erickia wrote:

> I know how to change the way the date varible displays but how can I change the way a recordset date field displays? I would like it to look like "May 16th 2005."

When asking questions like this, it's important to say which database
system you're using, because they all have different ways of doing it. I
happen to know from another post that you're using MySQL, so the answer
is simple:

SELECT column1, column2, DATE_FORMAT(date_column, '%M %D %Y')
FROM table

You can find details of the MySQL DATE_FORMAT function here:

[url]http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html#id2851311[/url]

--
David Powers
Author, "Foundation PHP 5 for Flash" (friends of ED)
Co-author "PHP Web Development with DW MX 2004" (Apress)
[url]http://computerbookshelf.com[/url]

[erickia]

I'm sorry. I obviously don't know enough to ask the question properly.
Dreamweaver created the following line when I insterted a field on the page.

<?php echo $row_Recordset1['SaleDate']; ?>

Where would I put the formating parameters. I'm assuming that at this point
they would be PHP rather then MySQL.

[David Powers]

erickia wrote:

> Where would I put the formating parameters. I'm assuming that at this point
> they would be PHP rather then MySQL.

<?php echo date($row_Recordset1['SaleDate'],'F jS Y'); ?>

--
David Powers
Author, "Foundation PHP 5 for Flash" (friends of ED)
Co-author "PHP Web Development with DW MX 2004" (Apress)
[url]http://computerbookshelf.com[/url]

[erickia]

Hmmm!!! Seems like that should have worked but it didn't. I had changed a line in the php.ini file about using the MySQL date instead of the PHP default. I'll try changing that back.

[erickia]

No, it is still displaying:
2002-09-18 00:00:00

[David Powers]

erickia wrote:

> No, it is still displaying:
> 2002-09-18 00:00:00

Hang on a moment. Of course, it will. The PHP date() function requires a
PHP timestamp, not a MySQL date or timestamp.

MySQL produces dates in a standard format, but if you have used a MySQL
timestamp, the format changed with the introduction of MySQL 4.1. Unless
I know what $row_Recordset1['SaleDate'] produces, I can't to create a
PHP function to handle it.

The most sensible approach is what I suggested in the first place -
rewrite your SQL query so that the formatting is done by MySQL (that's
what it's for).

--
David Powers
Author, "Foundation PHP 5 for Flash" (friends of ED)
Co-author "PHP Web Development with DW MX 2004" (Apress)
[url]http://computerbookshelf.com[/url]

[David Powers]

erickia wrote:

> I had changed a line in the php.ini file about using the MySQL date instead of the PHP default. I'll try changing that back.

There is no setting in php.ini for changing the MySQL date. There is one
for MSSQL, but that has no impact on MySQL - they are totally unrelated
programs.

--
David Powers
Author, "Foundation PHP 5 for Flash" (friends of ED)
Co-author "PHP Web Development with DW MX 2004" (Apress)
[url]http://computerbookshelf.com[/url]

[erickia]

; Specify how datetime and datetim4 columns are returned
; On => Returns data converted to SQL server settings
; Off => Returns values as YYYY-MM-DD hh:mm:ss
;mssql.datetimeconvert = On

This was copied from the php.ini file and is what I was refering to.

[David Powers]

erickia wrote:

> ; Specify how datetime and datetim4 columns are returned
> ; On => Returns data converted to SQL server settings
> ; Off => Returns values as YYYY-MM-DD hh:mm:ss
> ;mssql.datetimeconvert = On
>
> This was copied from the php.ini file and is what I was refering to.

Yes, it's exactly what I was talking about. Look at the last line you
have copied:

;mssql.datetimeconvert = On

It says *mssql*, not mysql. It's also in the [MSSQL] section of php.ini.
The MySQL and MySQLi commands are much higher up, and contain no
settings that affect the date.


--
David Powers
Author, "Foundation PHP 5 for Flash" (friends of ED)
Co-author "PHP Web Development with DW MX 2004" (Apress)
[url]http://computerbookshelf.com[/url]

[erickia]

SELECT column1, column2, DATE_FORMAT(date_column, '%M %D %Y')

Let me see if I understand. I've already performed a query to retrieve the
record I want. Now I should perform another query that will return just the
particular date field within that record with formating set to how I want the
field to display using the highlighted text above to replace the * in the
query. If that is correct, can I do it in the form "SELECT
senddate,DATE_FORMAT(senddate,'%M %D %Y') FROM table" IOW, I don't understand
what "column1," "column2" or "date_column" stand for.

[David Powers]

erickia wrote:

> IOW, I don't understand
> what "column1," "column2" or "date_column" stand for.

They stand for the names of the columns in your table.

Let's say you currently have a SQL query like this:

SELECT * FROM myrecords

Because you want to retrieve the date in a special format, you need to
name each column separately, instead of using *.

In your recordset dialog box, click the Advanced button. In the Database
Items box at the bottom, expand Tables, and then the table you want to
use. Highlight the first column you want to include in your recordset,
and click SELECT. Do the same for every column you want to include.

This will build a SQL query in the SQL field at the top. In the Advanced
interface, Dreamweaver usually adds the name of the table to each
column, so you'll end up with something like this:

SELECT myrecords.name, myrecords.order, myrecords.senddate
FROM myrecords

Surround the senddate column with the DATE_FORMAT I gave you:

SELECT myrecords.name, myrecords.order,
DATE_FORMAT(myrecords.senddate,'%M %D %Y')
FROM myrecords

Make sure there is *no space* between DATE_FORMAT and the opening
parenthesis. Your recordset will now contain the formatted date.

--
David Powers
Author, "Foundation PHP 5 for Flash" (friends of ED)
Co-author "PHP Web Development with DW MX 2004" (Apress)
[url]http://computerbookshelf.com[/url]

[erickia]

$query_Recordset1 = "SELECT
City,Address1,DocumentPrice,Buyer,Seller,DATE_FORM AT(SaleDate,' %M %D %Y') FROM
land WHERE compid = 9999";

OK, this is the query, which seemed to work.

<?php echo $row_Recordset1['SaleDate']; ?>

And this was the code to display the date, which didn't work. Using "Live
Data" to check the coding, I get an error saying "Undefined index SaleDate in
C:\htdocs" etc. The other fields display fine.

[erickia]

I found the answer. Don't have to change the SELECT string afterall. Just had
to use the following syntax for the formatting.

<p><?php echo date(' F jS Y',strtotime($row_Recordset1['SaleDate'])); ?>

[David Powers]

erickia wrote:

> And this was the code to display the date, which didn't work. Using "Live
> Data" to check the coding, I get an error saying "Undefined index SaleDate in
> C:\htdocs" etc. The other fields display fine.

Right, I see where the problem lies. You need to assign an alias to the
SaleDate column like this:

$query_Recordset1 = "SELECT
City,Address1,DocumentPrice,Buyer,Seller,
DATE_FORMAT(SaleDate,' %M %D %Y') AS SaleDate
FROM land WHERE compid = 9999";

That should now work. What happened was that MySQL was returning the
SaleDate column with DATE_FORMAT as part of the column name. By adding
"AS SaleDate", it will use the original name.

--
David Powers
Author, "Foundation PHP 5 for Flash" (friends of ED)
Co-author "PHP Web Development with DW MX 2004" (Apress)
[url]http://computerbookshelf.com[/url]

[David Powers]

erickia wrote:

> I found the answer. Don't have to change the SELECT string afterall. Just had
> to use the following syntax for the formatting.
>
> <p><?php echo date(' F jS Y',strtotime($row_Recordset1['SaleDate'])); ?>

Yes, that's a valid way of doing it, although getting MySQL to do it for
you is more efficient. It's also useful to know how MySQL date and time
functions work.

--
David Powers
Author, "Foundation PHP 5 for Flash" (friends of ED)
Co-author "PHP Web Development with DW MX 2004" (Apress)
[url]http://computerbookshelf.com[/url]

[Michael Fesser]

.oO(erickia)

>I found the answer. Don't have to change the SELECT string afterall. Just had
>to use the following syntax for the formatting.
>
> <p><?php echo date(' F jS Y',strtotime($row_Recordset1['SaleDate'])); ?>

If a database is capable of returning formatted dates you should use it.
It's easier, faster and more reliable. Additionally PHP uses Unix
timestamps, which are restricted in range.

Micha