DataTables Date Format Sorting Issue

Komuraiah A commented on Jun 3

Hi

I am struggling to get the DataGrid to sort date format correctly. As you can see in the below ss the datetime is sorting by only the date(from 31st-01st & 01st-31st) not by date-month-year togother. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ my code was placed as: in myreport.php page

			->pipe(new \koolreport\processes\CopyColumn([
			"createdDate_2" => "createdDate",
			"updatedDate_2" => "updatedDate",
		]))
            ->pipe($this->dataStore("data1"));

in myreport.view.php page :

"updatedDate"=>array(
	"label"=>"Updated Date",
   	 "type"=>"datetime",
   	 "format"=>"Y-m-d H:i:s",
    	"displayFormat"=>"d-m-Y H:i:s" ,
	"data-order" => "updatedDate_2", 
	"formatValue"=>function($updatedDate, $row)

same for two columns and tried with out the format also but still not working ,after tried many trials ..(no solution)

The date of 03-06-2024 Came in the middle acually this was the latest date.

Can you please help me in solving this?

Thanks in advance!

Sebastian Morales commented on Jun 4

You would need to include "updatedDate_2" in your DataTables' columns as well and hide it if needed:

DataTables::create(array(
    ...
    "columns" => array(
        "updatedDate" => ...
        "updatedDate_2" => array("visible" => false),

By the way pls show us the raw value of "updatedDate_2" column.

Komuraiah A commented on Jun 4

As you said i had placed the column in my code as:

"updatedDate"=>array(
	"label"=>"Updated Date",
    "type"=>"datetime",
    "format"=>"Y-m-d H:i:s",
    "displayFormat"=>"d-m-Y H:i:s" ,
	"data-order" => "updatedDate_2", 
	"formatValue"=>function($updatedDate, $row)
					 {
							$readby=$row['readby'];
							$updatedDate = $row['updatedDate'];
							$readdate = $row['readdate'];
						 if($updatedDate=='null')
						 {
							 return '-';
						 }
						 elseif($updatedDate=='0')
						 {
							 return '-';
						 }
						 elseif($updatedDate=='-')
						 {
							 return '-';
						 }
						  if($readby === null OR $readdate < $updatedDate) {
						 return "<span style='font-size: 11x; font-weight: bold; color: black;'>$updatedDate</span>"; 
						  }
						   elseif ($readdate > $updatedDate){
						 return  "<span style='font-size: 11x; color: black;'>$updatedDate</span>"; 
						  } 
					 }
	),

	"updatedDate_2"=>array(
	"label"=>"Updated Date_2", 
	//"visible" => false,
	),

and the raw data is shown in the Screenshot/image:

its not working total "dd-mm-yyyy" ,its taking only the "dd-00-0000" date. what to do now?

Sebastian Morales commented on Jun 5

It seems your raw datetime data is formatted as "d-m-Y H:i:s", which is not suitable for sorting. I would change the format to "Y-m-d H:i:s" like this first in the report setup:

		->pipe(new \koolreport\processes\CopyColumn([
			"createdDate_2" => "createdDate",
			"updatedDate_2" => "updatedDate",
		]))
		->pipe(new \koolreport\processes\DateTimeFormat(array(
                    "updatedDate_2" => array(
                        "from" => "d-m-Y H:i:s",
                        "to" => "Y-m-d H:i:s"
                    ),
                )))

Then in report view's DataTables you can sort column "updatedDate" by "updatedDate_2" values.

Komuraiah A commented on Jun 5

Thank you it's working now

KoolReport's Forum

DataTables Date Format Sorting Issue #3308

Build Your Excellent Data Report