Skip to main content
R
RichardODreamweaver
Inspiring
November 28, 2006
Answered

Datetime field format American / English

  • November 28, 2006
  • 6 replies
  • 837 views
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?
This topic has been closed for replies.
Correct answer Newsgroup_User
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/

6 replies

R
RichardODreamweaverAuthor
Inspiring
December 1, 2006
Many thanks again David - I will always attach code to the main message body in future.

Love the conditional statement!
N
Newsgroup_UserCorrect answer
Inspiring
December 1, 2006
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/
R
RichardODreamweaverAuthor
Inspiring
December 1, 2006
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
R
RichardODreamweaverAuthor
Inspiring
November 29, 2006
So I don't appear as a non tryer, I have inserted some script that I think it might be....

probably way off...
N
Newsgroup_User
Inspiring
November 28, 2006
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/
R
RichardODreamweaverAuthor
Inspiring
November 29, 2006
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!
N
Newsgroup_User
Inspiring
November 28, 2006
.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
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices