Dates and Databases

You can retrieve dates (and times) from an SQL database in any text string format you like simply by using the SQL DATE_FORMAT command in your query to specify the required format. Of course unless you know for certain that you will only ever need the date in a particular format you will be better off simply retrieving the default format and converting it to a PHP Date field so as to allow maximum flexibility in the subsequent processing, using the PHP format command to convert to the desired format when you are ready to display the date.

If we assume that we have retrieved a date from a table in our database into a text string called $sqldate then we can convert that into a PHP date using:

$phpdate = new DateTime($sqldate);

We can then perform whatever manipulations we require on that date from within our PHP and then convert it back into a text string for display using:

$strdate = $phpdate->format('Y-m-d H:i:s');

The value we pass into the format can use any of the valid characters for specifying the various portions of the date and time in whichever order that we want them to appear when we display the date. In this example code we have converted the date and time back into exactly the same format that we will have received it in if we didn't format the date when we retrieved it from the database - which is exactly the order that the parts of the date and time need to be in both to create a new date object based on the DateTime class in PHP and is also the format that a date/time string needs to be in to load it into a table in the database (and the default format that it will be retrieved into from the database).


This article written by Stephen Chapman, Felgall Pty Ltd.

go to top

FaceBook Follow
Twitter Follow