php - Date format coming in weird way in CSV file using codeigniter
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.
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:-
Why is such inconsistency of display of date format in CSV data? What am I doing wrong?
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:
CSV is a very loose format and does not provide any standard way to format dates. Yet I suggest something like YYYY-MM-DD.