jqGrid has a elegant way to work with dates. The reason for this special handling is that the date format that the underlaynd Database accept in most casses differ from those presented on the client side. If you use Microsoft SQL Server please refer here if you work with dates.

We consider two types of dates - Date and DateTime.
By default the format of the date and datetime accepted server side is Y-m-d for the date and Y-m-d H:i:s for datetime.
The default format of date and datetime represented client side is d/m/Y and d/m/Y H:i:s
For more information on date formatting in PHP refer here

You can change these formats using the following methods:

for the underlaing database and

for the client side representation

In order to synchronize date formats between the client and server a datearray variable should be used.
This is 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 operations are performed

In order to ilistrate this we again will refere to our example

Let 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 correct 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.

Related methods


Related variables