How to get date_format() to work properly

**traq** · 05-31-2012, 03:08 AM

Are you pulling the values from your DB?

MySQL defaults to 0000-00-00 when it can't understand a given date. Likewise, strtotime() defaults to 1969-12-31 when it doesn't understand a date. (I don't know why you're getting "-0001-11-30"; I couldn't reproduce that.)

PHP Code:


<?php

//  are you _sure_ that   $client['edited'] === '0000-00-00'  ...?
//  try passing '0000-00-00' directly ...?

echo date( "Y-m-d",strtotime( '0000-00-00' ) );
// output: 1969-12-31

echo date( "Y-m-d",strtotime( '2012-05-30' ) );
// output: 2012-05-30

**kuau** · 05-31-2012, 03:39 PM

Yes, I am pulling the values from a mySQL database. When the record has never been edited, the `edited` field is NULL. It displays as 0000-00-00 00:00:00. I am sure about that. When I use echo $client['edited'] with no formatting, that is what I get.

**traq** · 05-31-2012, 07:49 PM

odd - I still get 1969-12-31, even with the 00:00:00 part. That is the correct behavior, according to php.net. (MySQL giving "0000-00-00 00:00:00" for NULL is also the correct behavior.)

You said you did a conditional (if) check on the $client['edited']; what did that look like?
Something like this should be sufficient...

PHP Code:


<?php

if( $client['edited'] == '0000-00-00 00:00:00' ){
    print "this entry has never been edited.";
    // or set it to today's date, or whatever
}

**kuau** · 05-31-2012, 11:17 PM

Dear traq: I'll try that again, but right now I thought I would try one of your earlier suggestions about doing the formatting in the SQL command. BTW, is it true that if you wish to specify one field that you have to name them all? I went from SELECT * to a huge list of columns.

Anyway, what I did is displaying as blank. I must be doing something else wrong now... oh, I figured it out... here is what works...

Code:

SELECT `client_id`, `agent`, `lastname`, `firstname`, ...etc... , date_format(`edited`,'%Y-%m-%d') AS `edited` FROM client

Then in the report: echo $client['edited']

It works! Yahoo! Thanks for all your help.

**traq** · 06-01-2012, 12:55 AM

Originally Posted by kuau

BTW, is it true that if you wish to specify one field that you have to name them all? I went from SELECT * to a huge list of columns. ...

I'm glad to hear you got it sorted out.

If I understand your question, no, you don't *have* to list all the columns - you can do like:

Code:

SELECT *,date_format(`edited`,'%Y-%m-%d') AS `edited_formatted`FROM client ...

While it's easier, I don't think it's "better," per se. But it does work (note that you can't use one of the actual column names if you do this -as `edited`won't work; as `edited_something` will- mysql will complain if there's a column name conflict).

**kuau** · 06-01-2012, 01:08 AM

Dear traq: Yes, that is exactly what I wanted to know (SELECT *,date_format...). Thank you.

The funny thing is that mysql didn't complain when I used an actual column name after AS. I did it successfully yesterday too when I had to re-run a script on a different column name so it saved me having to edit the second script which was great. I hope it is OK to keep doing it (?).

My report looks so good now!

**traq** · 06-01-2012, 04:28 AM

if you use *, it will become a problem: e.g.,

Code:

# this works just fine, as long as you don't also SELECT `example`
SELECT `one`,`two`,`three`,DATE_FORMAT( `example`,'%Y-%m-%d' ) as `example` ...

# however, this will cause an error:
SELECT *,DATE_FORMAT( `example`,'%Y-%m-%d' ) as `example` ...
# because you're trying to use `example` as an alias, 
#   when you've already selected a column with that name (via *).
# MySQL doesn't like the ambiguity.

SELECT *,DATE_FORMAT( `example`,'%Y-%m-%d' ) as `no_col_has_this_name` ...
# working fine again.

**kuau** · 06-01-2012, 07:47 AM

Dear traq: I just got back home so I didn't see this until now. I had already changed it this afternoon to...

Code:

$sql = "SELECT *, date_format(`edited`,'%Y-%m-%d') AS `edited` FROM client

That is my exact code. I swear to god it worked fine right off the bat and I just checked again and it is working perfectly. Maybe my version of mysql is more forgiving than yours. Thanks for solving my problem for me. Now if I can just get the array one solved, I'll be happy.