Manipulating date with "00" in it coming from mySQL

>>> "SELECT * FROM table WHERE EXTRACT(YEAR FROM `dt`) < 1971 ORDER BY dt ASC";
>>
>> 1. Don't do SELECT *. SELECT what you need. Trust me.
>> 2. I'd do WHERE YEAR(dt) < 1971. No idea though if it gives you any
>> performance or other advantages.
>>
>> If PHP's date type cannot handle '0' days or months (apparently it
>> can't), use a simple explode:

>>
>> $dt = "2009-00-00";
>> $date = explode("-", $dt);
>>
>> Then, $date[0] will hold "2009", $date[1] "00" and $date[2] "00". With
>> intval() you can cast and do arithmetic later.
>Thanks Bart. I tried your suggestion and I can get it to format the way
>that I want. However, in the cases where $date[2] is equal to 00, the
>month is printing out as one month earlier than it should.

>Example: date coming directly out of the database is 1971-11-00. When
>it is printed, it is coming out as 1971 October. Here's what I used to
>manipulate it:
>$dt = $row["dt"];
>$date = explode("-", $dt);
>//If array has a number for year and month but not day:
>if (($date[1] != 00) && ($date[2] == 00)) {
>        $title1 = date('Y F', strtotime($row["dt"]));

>}
>//If array has no number month and thus cannot have a number for day:
>if ($date[1] == 00) {
>        $title1 = $date[0];
>}
>//If array has a number for year and month and day:
>if (($date[1] != 00) && ($date[2] != 00)) {
>        $title1 = date('Y F d', strtotime($row["dt"]));
>}
>Where did I make an error to cause the month displayed to be wrong? I

>used strtotime because I could not get $date[1] to format correctly
>(using full month names) any other way.

>
>>> $dt = "2009-00-00";
>>> $date = explode("-", $dt);
>>> Then, $date[0] will hold "2009", $date[1] "00" and $date[2] "00". With
>>> intval() you can cast and do arithmetic later.
>> Thanks Bart. I tried your suggestion and I can get it to format the way
>> that I want. However, in the cases where $date[2] is equal to 00, the
>> month is printing out as one month earlier than it should.
>
> If you absolutely MUST feed this to date() or strtotime, fix the
> day number to 01 (or any valid day for that month) first.
>
>> Example: date coming directly out of the database is 1971-11-00. When
>> it is printed, it is coming out as 1971 October. Here's what I used to
>> manipulate it:
>> $dt = $row["dt"];
>> $date = explode("-", $dt);
>> //If array has a number for year and month but not day:
>> if (($date[1] != 00) && ($date[2] == 00)) {
>>         $title1 = date('Y F', strtotime($row["dt"]));
>
> Then change $date[2] to 01, implode it, and feed THAT to strtotime.
>
>> }
>> //If array has no number month and thus cannot have a number for day:
>> if ($date[1] == 00) {
>>         $title1 = $date[0];
>> }
>> //If array has a number for year and month and day:
>> if (($date[1] != 00) && ($date[2] != 00)) {
>>         $title1 = date('Y F d', strtotime($row["dt"]));
>> }
>> Where did I make an error to cause the month displayed to be wrong? I
>
> strtotime() is doing what it is supposed to do.
> If you want to stay in the same month, attach a valid day first.

> Gordon Burditt wrote:
>>>> $dt = "2009-00-00";
>>>> $date = explode("-", $dt);
>>>> Then, $date[0] will hold "2009", $date[1] "00" and $date[2] "00". With
>>>> intval() you can cast and do arithmetic later.
>>> Thanks Bart. I tried your suggestion and I can get it to format the
>>> way that I want. However, in the cases where $date[2] is equal to 00,
>>> the month is printing out as one month earlier than it should.
>> If you absolutely MUST feed this to date() or strtotime, fix the
>> day number to 01 (or any valid day for that month) first.
>>> Example: date coming directly out of the database is 1971-11-00.
>>> When it is printed, it is coming out as 1971 October. Here's what I
>>> used to manipulate it:
>>> $dt = $row["dt"];
>>> $date = explode("-", $dt);
>>> //If array has a number for year and month but not day:
>>> if (($date[1] != 00) && ($date[2] == 00)) {
>>> $title1 = date('Y F', strtotime($row["dt"]));
>> Then change $date[2] to 01, implode it, and feed THAT to strtotime.
>>> }
>>> //If array has no number month and thus cannot have a number for day:
>>> if ($date[1] == 00) {
>>> $title1 = $date[0];
>>> }
>>> //If array has a number for year and month and day:
>>> if (($date[1] != 00) && ($date[2] != 00)) {
>>> $title1 = date('Y F d', strtotime($row["dt"]));
>>> }
>>> Where did I make an error to cause the month displayed to be wrong? I
>> strtotime() is doing what it is supposed to do.
>> If you want to stay in the same month, attach a valid day first.
>
> Thanks Gordon. That explained a whole lot. It appears that since I do
> have some pre-1970 dates and I don't want to display unknown days as 01
> that using the date options of either PHP or mySQL won't do what I want.
>
> It appears that my best option will be to split the date into three
> fields in the database, one using the mySQL YEAR type and the other two
> using integers that I can manipulate with if statements. I put a test
> together using this and it is working the way that I want it to.
>
> I appreciate the assistance from all in helping me reach a solution to
> the problem.

>Thanks Gordon. That explained a whole lot. It appears that since I do
>have some pre-1970 dates and I don't want to display unknown days as 01
>that using the date options of either PHP or mySQL won't do what I want.

>It appears that my best option will be to split the date into three
>fields in the database, one using the mySQL YEAR type and the other two
>using integers that I can manipulate with if statements. I put a test
>together using this and it is working the way that I want it to.

> Álvaro G. Vicario wrote:
>> JackM escribió:
>>> I need a little direction in manipulating a date being pulled from a
>>> mySQL db. It's not something I usually need to do so I am at a loss
>>> on how to accomplish it.
>>> The date is stored in the yyyy-mm-dd format in a field named dt that
>>> is of the date type. Some of the entries are entered as 1970-00-00 or
>>> 1970-05-00 where an actual month and/or day are unknown. I am trying
>>> to manipulate those dates to drop the zeros and only print out the year.
>> A little hint:
>> SELECT
>> '2009-05-00',
>> YEAR('2009-05-00'),
>> MONTH('2009-05-00'),
>> DAY('2009-05-00'),
>> IF(DAY('2009-05-00')=0, YEAR('2009-05-00'), '2009-05-00')
>
> Are you saying that I need to run a second SELECT statement for just the
> date manipulation? I won't have the exact 2009-05-00 information to do
> that with each entry.