We consider two types of dates - Date and DateTime.
The default format of Date and DateTime types on the server-side is Y-m-d for date and Y-m-d H:i:s for datetime.
The default format of Date and DateTime types on the client-side(javascript) is d/m/Y and d/m/Y H:i:s
For more information on date formatting in PHP, please refer to here
You can change these formats using the following methods:
setDbDate
setDbTime
for the underlying database and
setUserDate
setUserTime
for the client side representation.
In order to synchronize date formats between the client and server, a datearray variable should be used.
This is an array which tells the script which fields are dates. The value(s) in the array should correspond to the name in colModel This conversion is used when a search or CRUD (create, read, update, delete) operations are performed
In order to ilistrate this we again will refere to rhis example.
Let's suppose that the date format accepted from the server is Y-m-d and at client the format should be m/d/Y.
In order to display and search correctly the html file should be changed this way.
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>PHP jqGrid Class Example</title> <link rel="stylesheet" type="text/css" media="screen" href="themes/redmond/jquery-ui-1.7.1.custom.css" /> <link rel="stylesheet" type="text/css" media="screen" href="themes/ui.jqgrid.css" /> <script src="js/jquery-1.3.2.min.js" type="text/javascript"></script> <script src="js/i18n/grid.locale-en.js" type="text/javascript"></script> <script src="js/jquery.jqGrid.min.js" type="text/javascript"></script> <script type="text/javascript"> jQuery(document).ready(function(){ .... // Craeate the grid manually jQuery("#grid").jqGrid({ "colModel":[ {"name":"OrderID","index":"OrderID","label":"ID","width":60, "key":true}, {"name":"OrderDate","index":"OrderDate", "formatter":"date", "formatoptions":{"srcformat":"Y-m-d", "newformat":"m/d/Y"} }, {"name":"CustomerID","index":"CustomerID"}, {"name":"Freight","index":"Freight"}, {"name":"ShipName","index":"ShipName"} ], "url":"querygrid.php", "datatype":"json", "jsonReader":{repeatitems:false}, "pager":"#pager" }); // Set navigator with search enabled. jQuery("#grid").jqGrid('navGrid','#pager',{add:false,edit:false,del:false}); ...... }); </script> </head> <body> ...... <table id="grid"></table> <div id="pager"></div> ....... </body> </html>
The changes in PHP script should be:
<?php require_once 'jq-config.php'; // include the jqGrid Class require_once "php/jqGrid.php"; // include the PDO driver class require_once "php/jqGridPdo.php"; // Connection to the server $conn = new PDO(DB_DSN,DB_USER,DB_PASSWORD); // Create the jqGrid instance $grid = new jqGrid($conn); // Write the SQL Query $grid->SelectCommand = 'SELECT OrderID, OrderDate, CustomerID, Freight, ShipName FROM orders'; // set the user date format to m/d/Y $grid->setUserDate('m/d/Y'); // tell the grid which field is date $grid->datearray = array('OrderDate'); $grid->dataType = "json"; $grid->queryGrid(); ?>
That is all. Now your display and search will be correct.