PHP Date Export To CSV Format

Question

After export and when I open the CSV up, you'll notice column L formats only some of the dates properly. This happend on a couple other date columns as well. Is there anything I can do with PHP to ensure all dates are returned properly? Below id my function:

public function formatDateCI($date) {

    // If Birthdate Column
    if(strlen($date) == 10){
        $date = explode('-',$date);
        $m = $date[0];
        $d = $date[1];
        $Y = $date[2];
        $date = $Y.'-'.$m.'-'.$d;
    }

    $date = new DateTime($date);
    // Final Format 1983-24-12 00:00:00
    return date_format($date, 'Y-d-m H:i:s');
}

Column L Date Format Issue

How does the $date variable look like if it doesn't enter the If condition? — Gabriel, Oct 07 '14 at 14:20
Were does `$date` come from is it a datetime column from a database? — RiggsFolly, Oct 07 '14 at 14:22
Gabriel, if it doesn't enter the if condition it looks like 2014-08-07 21:04:19, Riggs, the brithdate column in the database is not a datetime. The other columns are datetime — Hector, Oct 07 '14 at 14:25
But i still see the same issue on the other columns that are datetime — Hector, Oct 07 '14 at 14:26
You can force Excel to use text format with this fix : http://stackoverflow.com/a/165052/911718 — Kevin Labécot, Oct 07 '14 at 14:30

Gabriel · Accepted Answer · 2014-10-07T14:57:06.400

3

Try:

date('Y-m-d H:i:s', strtotime($date));

Instead of:

$date = new DateTime($date);

>

Be sure to try an if else structure:

if(strlen($date) == 10){
        $date = explode('-',$date);
        $M= $date[0];
        $D = $date[1];
        $Y = $date[2];
       //this $date = $Y.'-'.$m.'-'.$d;
       //or this =>
       $date = date('Y-m-d H:i:s', mktime($h, $m, $s, $M, $D, $Y));
    }else{
         $date = date('Y-m-d H:i:s', strtotime($date));
    }

    return $date;

edited Oct 07 '14 at 14:57

answered Oct 07 '14 at 14:41

Gabriel

772
1
13
37

This worked too, but some birthdates are returning as 1970-01-01 00:00:00 – Hector Oct 07 '14 at 14:51
@Hector You need to have a unified format for dates, try an if else structure like see my edit – Gabriel Oct 07 '14 at 14:53

RiggsFolly · Answer 2 · 2014-10-07T15:06:34.537

0

Simple solution would be to do just this

public function formatDateCI($date) {
    return date('%Y-%m-%d 00:00:00', strtotime($date));
}

In which case you probably dont even need to make it a method as it will fit nicely in the calling code.

ADDITIONAL INFO

If you are using an American date format you will have to correct the format for strtotime() as it expects USA format dates to have a / seperator and not a - seperator. If it see a - seperator it expects a logical date format of some sort.

public function formatDateCI($date) {
    // correct USA date format for strtotime
    $date = str_replace('-','/',$date);
    return date('%Y-%m-%d 00:00:00', strtotime($date));
}

edited Oct 07 '14 at 15:06

answered Oct 07 '14 at 14:28

RiggsFolly

93,638
21
103
149

Call to undefined function strtodate() – Hector Oct 07 '14 at 14:43
SORRY, my woops, corrected to `strtotime()` I guess I should not have had that pint with my dinner. – RiggsFolly Oct 07 '14 at 14:45
Thanks, this worked on all the columns that were datetime in the DB, but now many of the birthdate fields are returning as 1970-01-01 00:00:00 – Hector Oct 07 '14 at 14:50
What exactly is in the birthdate column, show some examples where is goes wrong? – RiggsFolly Oct 07 '14 at 14:57
If `birthdate` is an optional field i.e. can it be left blank by user? if so what is the default value set by your database for that column. – RiggsFolly Oct 07 '14 at 15:01
This was the DOB column example '12-24-1983', Got it working with the if statement above. The strtotime() did fix my other issues, thanks! – Hector Oct 07 '14 at 15:01

PHP Date Export To CSV Format

2 Answers2

Linked