Page 1 of 2

php/mysql date display problem

Posted: Tue May 25, 2004 10:15 am
by theperfectdrug
Hi!
I'm making articles section using php/mysql; so I've got a problem in displaying dates;
In some of the articles I don't have complete date, like in date(DATE type 0000-00-00) field there is only a year or year and month; some of them are complete(year-month-day).
I'm using following query to format dates:
$sql = "select headline, body, sid, date_format(date, '%M %e, %Y') as fdate from articles order by date desc";
So dates look like this(in case if date is complete year-month-day):

EX: October 3, 2002

But if date is not complete (month-year) script returns:

EX: October 0, 2002

In case when there is only a year in date field (2002-00-00) it shows no date at all.

I just want script to return complete date when it's available, when there is only -month and year- in date field I want it to return date in following format, EX: October, 2002 instead of October 0, 2002...In case if there is only a year in Date field EX: 2002; I want script to show year, instead of empty space.

I've tryed a few methods, in all cases if I managed to format date (month day, year) or (month, year), I'm still getting empty space instead of year.

Any ideas?

Thanks

Posted: Tue May 25, 2004 10:38 am
by launchcode
What format is your date column in?

Posted: Tue May 25, 2004 10:46 am
by theperfectdrug
What format is your date column in?
colum is DATE type(0000-00-00), so format is somethin' like this year-month-day, EX: 2002-01-25.

Posted: Tue May 25, 2004 10:55 am
by launchcode
You could just do it like this?

Code: Select all

select date,
date_format(date, '%Y') as year,
date_format(date, '%m') as month,
date_format(date, '%d') as day
from dates
Which would give you 3 new columns from which you can work out on a code level what to do?

Posted: Tue May 25, 2004 11:07 am
by theperfectdrug
thanks!
now can you tell me what would be the best way to work out with this individual columns, maybe IF/else....?

Posted: Tue May 25, 2004 11:34 am
by launchcode
Just check to see if month/day == 00? :)

Posted: Tue May 25, 2004 11:35 am
by theperfectdrug
Thanks a lot,
I'll give it a try...thx again

Posted: Tue May 25, 2004 11:53 am
by theperfectdrug
aha everything works fine, but now I want to convert dates (EX: 05 21, 2002 to May 21, 2002) ? :)

Posted: Tue May 25, 2004 11:54 am
by feyd
%M I believe.

Posted: Tue May 25, 2004 11:55 am
by theperfectdrug
ohh...stupid question... :D

Posted: Tue May 25, 2004 12:04 pm
by theperfectdrug
I wrote lil' code, with if/else stuff, it works fine but I'm getting ',' before year (In case when there's only year in Date column EX: 2002-00-00)

Code: Select all

if ($day == ""){
echo "$month, $year";
}
elseif ($month == "" || $day == ""){
echo "$year";
}
else{
echo "$month $day, $year";
}
Here's Example:
, 2002

is there any way to fix this problem?

Posted: Tue May 25, 2004 12:08 pm
by feyd
might want to check against "00" instead of empty strings.. I dunno for sure though.

Posted: Tue May 25, 2004 12:11 pm
by theperfectdrug
might want to check against "00" instead of empty strings.. I dunno for sure though.
hm...I'm getting same result with 00.

Posted: Tue May 25, 2004 12:13 pm
by feyd
with quotes?

Posted: Tue May 25, 2004 12:14 pm
by theperfectdrug
same result in all cases, both with 00 and quotes. :(