Formatting Date DataType

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
AliasBDI
Forum Contributor
Posts: 286
Joined: Fri Nov 15, 2002 10:35 am
Location: Spring, TX, USA

Formatting Date DataType

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

AliasBDI
Forum Contributor
Posts: 286
Joined: Fri Nov 15, 2002 10:35 am
Location: Spring, TX, USA

YES!

Post by AliasBDI »

Got it! Thanks. I changed my query to read:

Code: Select all

SELECT *, DATE_FORMAT('datecolumn','%a, %b %e, %Y') FROM table
AliasBDI
Forum Contributor
Posts: 286
Joined: Fri Nov 15, 2002 10:35 am
Location: Spring, TX, USA

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

huh?
AliasBDI
Forum Contributor
Posts: 286
Joined: Fri Nov 15, 2002 10:35 am
Location: Spring, TX, USA

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

what was the SELECT query for that?
AliasBDI
Forum Contributor
Posts: 286
Joined: Fri Nov 15, 2002 10:35 am
Location: Spring, TX, USA

Post 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']); ?>
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
AliasBDI
Forum Contributor
Posts: 286
Joined: Fri Nov 15, 2002 10:35 am
Location: Spring, TX, USA

Post 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
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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....
AliasBDI
Forum Contributor
Posts: 286
Joined: Fri Nov 15, 2002 10:35 am
Location: Spring, TX, USA

Post by AliasBDI »

I got it...

DATE_FORMAT(eventDateIN, '%m-%d-%Y') AS eventDateINshow
AliasBDI
Forum Contributor
Posts: 286
Joined: Fri Nov 15, 2002 10:35 am
Location: Spring, TX, USA

Post by AliasBDI »

Another question. What if the data type is datetime? It echos this format = 0000-00-00 00:00:00
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

DATE_FORMAT() will output the same as if it were a DATE, pretty much.
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post 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
Post Reply