php - Date format coming in weird way in CSV file using codeigniter

480

I have a codeigniter function to create a CSV file for the user data from a DB.

My function is this:-

function user_CSV()
{
    $filename = 'CSV_User_Report.csv';      
    $this->load->dbutil();      
    $this->load->helper('file');        
    $this->load->helper('download');        
    $delimiter = ",";        
    $newline = "\r\n";  
    $queryUser = "SELECT user_name AS Name, 
                    user_username AS Username, 
                    user_mobile AS Mobile, 
                    DATE_FORMAT( user_added_date, '%d-%m-%Y' ) AS `Joining Date`,
                    DATE_FORMAT( user_last_activity, '%d-%m-%Y' ) AS `Last Acitivity`
                FROM tbl_user"; 
    $dataResult = $this->db->query($queryUser);
    if($dataResult->num_rows()>0)       
    {   
        $data = $this->dbutil->csv_from_result($dataResult, $delimiter, $newline);      
    }   
    if(force_download($filename, $data))        
    {           
        redirect('admin/user');     
    }
}

Now, this one is creating the CSV file. But there is a severe issue. To illustrate that, I am sharing the screenshot of the CSV. enter image description here

As you can see, some of the Joining Date is coming in dd/m/YYYY format, and some dd-mm-YYYY format.

But, when I am trying to see the query in phpmyadmin, I am getting the result like this:- enter image description here

Why is such inconsistency of display of date format in CSV data? What am I doing wrong?

886

Answer

Solution:

My educated guess, without really looking at your code, is that your Spreadsheet software is assuming that dates come in American format (MM-DD-YYYY) so it's doing two things:

  • If day is 12 or less it misreads the date and swaps day and month
  • If day is greater than 13 it think it isn't a valid date and displays as string

CSV is a very loose format and does not provide any standard way to format dates. Yet I suggest something like YYYY-MM-DD.

People are also looking for solutions to the problem: php - Modifying query in Wordpress search

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.