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ї'eventDateINshow']; ?>
echos:
This code:
Code: Select all
<?php echo $row_eventsLISTmodї'eventDateIN']; ?>
echos (literal record):
This code:
Code: Select all
<?php echo date('m-d-Y', $row_newsLISTї'eventDateINshow']); ?>
...and this code:
Code: Select all
<?php echo $row_newsLISTї'eventDateINshow']; ?>
echos:
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)){
echo date("l M d, Y", strtotime($robertї'date']));
}
you could then add some time stuff in there too if you wanted
Burr