DataTables Date Format Sorting Issue

Komuraiah A commented on Jun 3, 2024

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, 2024

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, 2024

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, 2024

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, 2024

Thank you it's working now

KoolReport's Forum

DataTables Date Format Sorting Issue #3308

Build Your Excellent Data Report