Datetime field format American / English

[RichardODreamweaver]

LAMP system

I have a table with a datetime column which is in YYYY-MM-DD HH:MM:SS format.

As we are in sunny United Kingdom, I would like the fields to be formatted to DD-MM-YYY HH:MM:SS.

I have tried the application extension but the results all come through as 1/1/1970 instead of displaying the correct data.

Is this a collation issue (currently in utf8_general_ci) or is there a simple way to format both display fields and form input fields?

[Newsgroup_User]

.oO(RichardODreamweaver)

> I have a table with a datetime column which is in YYYY-MM-DD HH:MM:SS format.

Good.

> As we are in sunny United Kingdom, I would like the fields to be formatted to
>DD-MM-YYY HH:MM:SS.
>
> I have tried the application extension but the results all come through as
>1/1/1970 instead of displaying the correct data.
>
> Is this a collation issue (currently in utf8_general_ci) or is there a simple
>way to format both display fields and form input fields?
>
> <?php echo date('l, d/m/Y',$row_contactnotes['ACTDATE']); ?>

The date() function requires a Unix timestamp to work properly. You
could let the database convert the date to a timestamp or use PHP's
strtotime() function, but even simpler would be to let the database
return an already formatted date. Have a look at DATE_FORMAT().

12.5. Date and Time Functions
http://www.mysql.org/doc/refman/5.1/en/date-and-time-functions.html

Micha

[Newsgroup_User]

RichardODreamweaver wrote:
> I have a table with a datetime column which is in YYYY-MM-DD HH:MM:SS format.
>
> As we are in sunny United Kingdom, I would like the fields to be formatted to
> DD-MM-YYY HH:MM:SS.

You can't - at least not in the MySQL database. MySQL accepts one date
format only - the ISO recommended YYYY-MM-DD HH:MM:SS.

Store dates in that format, and use the MySQL DATE_FORMAT() function to
display the date and time in whatever format you want.

SELECT DATE_FORMAT(dateColumn, '%e%D %M %Y') as dateColumn
FROM myTable

That will format your date as 28th November 2006. Very powerful.

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

--
David Powers, Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

[RichardODreamweaver]

quote:

---

Originally posted by: Newsgroup User
You can't - at least not in the MySQL database. MySQL accepts one date
format only - the ISO recommended YYYY-MM-DD HH:MM:SS.

---

That would explain a lot and why the above reply had little effect.

quote:

---

Originally posted by: Newsgroup User
Store dates in that format, and use the MySQL DATE_FORMAT() function to
display the date and time in whatever format you want.

SELECT DATE_FORMAT(dateColumn, '%e%D %M %Y') as dateColumn
FROM myTable

That will format your date as 28th November 2006. Very powerful.

---

OK - forgive my incredible ignorance but I have read through the URL page that you offered and it was a little over my head!

I am a simple DWMX interface builder and do not understand so much of the scripting....

Isn't the SELECT statement for building queries rather than formatting fields (both display fields and form input fields)?

As a result, I do not know where to insert this in the php script..

Doh!

[Newsgroup_User]

On 29 Nov 2006 in macromedia.dreamweaver.appdev, RichardODreamweaver
wrote:

> Isn't the SELECT statement for building queries rather than
> formatting fields
> (both display fields and form input fields)?

It can also be used for formatting output, very powerfully.

> As a result, I do not know where to insert this in the php script..

Right where David told you to:

SELECT DATE_FORMAT(dateColumn, '%e%D %M %Y') as dateColumn
FROM myTable

To expand on that slightly:

SELECT field1, field2, ..., DATE_FORMAT(dateColumn, '%e%D %M %Y') AS dateColumn
FROM myTable

--
Joe Makowiec
http://makowiec.net/
Email: http://makowiec.net/email.php

[Newsgroup_User]

RichardODreamweaver wrote:
> I am a simple DWMX interface builder and do not understand so much of the
> scripting....

Unless you learn the basics of PHP and MySQL, you are going to be
extremely limited in what you can achieve. Dreamweaver server behaviors
are intended to provide basic functionality. To go beyond that, you have
to write your own scripts or adapt Dreamweaver's. If you haven't already
invested in a book to teach you the basics, you might want to consider
either "Foundation PHP with Dreamweaver 8" or "PHP Solutions". There are
other books, but I can offer some practical help with mine by pointing
you to a particular page and telling you how to adapt a script.

> Isn't the SELECT statement for building queries rather than formatting fields
> (both display fields and form input fields)?

All databases have functions that can be used to manipulate data in
combination with SQL queries. As I said before, MySQL stores dates in
one format only: YYYY-MM-DD HH:MM:SS. You use the MySQL DATE_FORMAT()
function in combination with a SELECT query to transform the stored date
into the format you want.

The URL I gave you covers all MySQL date and time functions. If you
search for DATE_FORMAT on that page, you will see that it uses a format
string to build the correct format. That page also lists the available
format specifiers. I gave just one example of how it could be used.

> As a result, I do not know where to insert this in the php script..

You put it in the SQL query. When creating the query in the Recordset
dialog box, you need to select the Advanced button and build the query
yourself.

--
David Powers, Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

[RichardODreamweaver]

quote:

---

Originally posted by: Newsgroup User
You put it in the SQL query. When creating the query in the Recordset
dialog box, you need to select the Advanced button and build the query
yourself.

---

Ahhhhh.... all becomes clear(er)!

Thanks David - I think I'll have to do some reading up. I have just been tasked with creating an intranet LAMP CRM system in 2 weeks and with just standard htm knowledge in DW, it was quite a tall challenge!

[RichardODreamweaver]

So I don't appear as a non tryer, I have inserted some script that I think it might be....

probably way off...

[RichardODreamweaver]

I have finally managed to reach an answer...

<td><font size="2" face="Arial"><?php echo date('D, d/m/Y H:i',strtotime($row_tasklistquery['ACTDATE'])); ?></font></td>


I needed to express the string from the recordset as a timestamp value which could then be output using the usual formatting.

The only problem is that if the field has an empty string, it returns a value of 1/1/1970... hmmm

[Newsgroup_User · Accepted solution]

RichardODreamweaver wrote:
> I have finally managed to reach an answer...
>
> see code.
>
> I needed to express the string from the recordset as a timestamp value which could then be output using the usual formatting.

Two things, Richard. First of all, do NOT use the attach code feature in
the forum web interface. Your code is stripped out when displayed in a
newsreader. I took the trouble to look at the web interface to see your
original message. Not only has the code been stripped out, but so has
the final paragraph of your post. The problems with the web/newsreader
interface are not your fault, but the web interface is so slow, most
long-term posters here refuse to use it. As a result, many of your
questions will be ignored.

Secondly, your "solution" is inefficient as it involves grabbing the
result from the database, converting it to a Unix timestamp, and then
using the PHP date() function to convert it again. If you read the
earlier posts from Joe Makowiec and me, you could accomplish the whole
thing in a single operation with the MySQL DATE_FORMAT() function.
You're making extra work both for yourself and for the web server.

Now, that question that was stripped out. There are two reasons you
might get 1/1/1970: either the field is empty, or more likely that it
contains an invalid date. To check for that, you need to wrap your code
in a conditional statement like this:

<?php
if (!empty($row_tasklistquery['ACTDATE']) ||
substr($row_tasklistquery['ACTDATE'],0,4) != '0000') {
// code to display date
}
else {
// code to deal with empty date
}

--
David Powers, Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

[RichardODreamweaver]

Many thanks again David - I will always attach code to the main message body in future.

Love the conditional statement!