Page 1 of 1

Formatting Date DataType

Posted: Thu Jan 20, 2005 9:04 pm
by AliasBDI
I have a column in a MySQL database with a DATE as the data type. When outputting, it appears like "2005-01-23". I am on a Windows machine running the latest PHP. I am trying to make it appear like this, "Sunday, Jan. 23, 2004". Any ideas?

Posted: Thu Jan 20, 2005 9:07 pm
by feyd

YES!

Posted: Fri Jan 21, 2005 6:56 am
by AliasBDI
Got it! Thanks. I changed my query to read:

Code: Select all

SELECT *, DATE_FORMAT('datecolumn','%a, %b %e, %Y') FROM table

Posted: Wed Feb 16, 2005 9:52 pm
by AliasBDI
Actually on second thought it was not working. I found only help on DateTime and not just Date (for Windows Servers using MySQL). The record outputs this format 0000-00-00 when it has not SQL formatting. Any ideas?

Posted: Wed Feb 16, 2005 10:38 pm
by feyd
huh?

Posted: Thu Feb 17, 2005 7:09 am
by AliasBDI
It's like this. If I echo the dry record, it appears like this: "2005-01-20". When I used the MySQL code posted above, I would get the same dry output as before. Why? Because my "DATE_FORMAT(..)" statement did not have a "AS showDate" so I could not echo the actual formatted record. When I figured that out, I echoed the formatted record and nothing appeared. So it did not work at all. I was just echoing the actual record without any formatting.

The datatype is DATE. It appears like "0000-00-00". I need it to appear like "MM-DD-YYYY".

By the way, I'm on a Windows server if that matters. 2000 I believe.

Posted: Thu Feb 17, 2005 8:30 am
by feyd
what was the SELECT query for that?

Posted: Thu Feb 17, 2005 6:38 pm
by AliasBDI
Query:
SELECT *, UNIX_TIMESTAMP(modifyDate) AS modifyDate FROM con_news

OR

SELECT *, DATE_FORMAT('modifyDate','%a, %b %e, %Y') AS modifyDate FROM con_news

Echo:
<?php echo date('m-d-Y', $row_newsLIST['modifyDate']); ?>

Posted: Thu Feb 17, 2005 6:44 pm
by feyd
your second select will generate a null datetime, as you are passing a string, not a field reference, to DATE_FORMAT() .. the first, should basically be okay though, provided the date is valid for the OS.

Posted: Thu Feb 17, 2005 7:09 pm
by AliasBDI
Here is what I get with this query:

Code: Select all

SELECT *, UNIX_TIMESTAMP(eventDateIN) AS eventDateINshow FROM con_events
and code:

Code: Select all

<?php echo $row_eventsLISTmod&#1111;'eventDateINshow']; ?>
echos:

Code: Select all

1109311200
This code:

Code: Select all

<?php echo $row_eventsLISTmod&#1111;'eventDateIN']; ?>
echos (literal record):

Code: Select all

2005-02-25
This code:

Code: Select all

<?php echo date('m-d-Y', $row_newsLIST&#1111;'eventDateINshow']); ?>
...and this code:

Code: Select all

<?php echo $row_newsLIST&#1111;'eventDateINshow']; ?>
echos:

Code: Select all

12-31-1969

Posted: Thu Feb 17, 2005 7:36 pm
by timvw
well, you select it as a date string... and then you feed it to a function that is expecting a unixtimestamp... that is why you are getting weird output...




anyway, as already suggested just

Code: Select all

SELECT DATE_FORMAT(eventDateIN, 'm-d-Y') AS eventDateINshow FROM con_events
allows you to skip the php manipulation....

Posted: Sat Feb 19, 2005 9:17 am
by AliasBDI
I got it...

DATE_FORMAT(eventDateIN, '%m-%d-%Y') AS eventDateINshow

Posted: Tue Feb 22, 2005 7:47 pm
by AliasBDI
Another question. What if the data type is datetime? It echos this format = 0000-00-00 00:00:00

Posted: Tue Feb 22, 2005 8:57 pm
by feyd
DATE_FORMAT() will output the same as if it were a DATE, pretty much.

Posted: Tue Feb 22, 2005 10:47 pm
by Burrito
you could manipulate it with php too using date() and strtotime().

something like

Code: Select all

$bob = mysql_query("select date from mytable");

if($robert = mysql_fetch_assoc($bob))&#123;
echo date("l M d, Y", strtotime($robert&#1111;'date']));
&#125;
you could then add some time stuff in there too if you wanted

Burr