PHPBuilder - Convert MySQL Date/Time into your own format.



RSS Twitter
Snippets Calendars Dates

Convert MySQL Date/Time into your own format.

by: Jay Perez
|
December 22, 2003

Version: 1.1

Type: Function

Category: Calendars/Dates

License: GNU General Public License

Description: This function allows you to pass in a MySQL Date/Time value and retrieve a Date/Time value according to the specified format. The following are example of the function call: echo formatSQLDateTime('2003-12-23 10:55:05', 0, 'd/m/Y - H:i:s'); echo formatSQLDateTime('2003-12-23', 1, 'jS F, Y'); echo formatSQLDateTime('10:55:05', 2, 'g:i a T'); Out put will be as shown below: 23/12/2003 - 10:55:05 Date Format is incorrect 10:55 am Singapore Standard Time Read the comments in the script for proper use of function. Comments/Suggestions are welcome.



<?php

function formatSQLDateTime ($sVar_sqlDateTime, $iVar_type, $sVar_format, $iVar_hourDifference = 0)
{

/*----------------------------------------------------------------------
************************************************************************
***********************COMMENTS NOT TO BE REMOVED***********************
************************************************************************
Function written by: Jayawi Perera (Mogul)
Contact: mogulofall@hotmail.com
Date Written: 23rd December, 2003
Date Updated: 23rd December, 2003
Version: 1.1

Version Changes
---------------

Version 1.1 - Added hour difference so that the time can be changed if required.



This function allows you to pass in a MySQL Date/Time value and retrieve
a Date/Time value according to the specified format.

$sVar_sqlDateTime contains the value retrieved from a MySQL Database.

$iVar_type is to specify what kind of Date/Time value is being passed.
0 - Date & Time
1 - Date
2 - Time

$sVar_format is to specify the format you want. The format used is the
default format used in PHP's date functions. Please refer to 
http://www.php.net/manual/en/function.date.php for the formats.

$iVar_hourDifference refers to change to be made to Date/Time specified.
It can range from -12 to 12. If the value is outside this range, the 
time difference will not be applied.

The following are example of the function call.

	echo formatSQLDateTime('2003-12-23 10:55:05', 0, 'd/m/Y - H:i:s');

	echo formatSQLDateTime('2003-12-23', 1, 'jS F, Y');

	echo formatSQLDateTime('10:55:05', 2, 'g:i a T');

	echo formatSQLDateTime('2003-12-23 10:55:05', 0, 'd/m/Y - H:i:s', -3);

	echo formatSQLDateTime('2003-12-23 10:55:05', 0, 'd/m/Y - H:i:s', 3);

	echo formatSQLDateTime('10:55:05', 2, 'g:i a', 2);

The first parameter is the SQL Date/Time you have retrieved from the 
Database. The second is the Type of your first parameter. The third 
parameter is the format you want the Date/Time to be displayed in. The
final parameter is the hour difference you may want to apply to the 
Date/Time. You may omit it in the function call if you do not need to
change the Date/Time.

You may amend the global variables used as necessary.
Or you may omit if them if you do not use any error checking.

Please note that in Windows Machines, the valid date range is only from
1970-01-01 to 2038-01-19. If your scripts are running on a Windows-based
server and you have a need to process dates beyond this range, this 
function will not work as expected.

Comments/Suggestions are welcome. Enjoy~
----------------------------------------------------------------------*/

global $bVar_gErrorStatus;	//For Global Error Checking
global $sVar_gErrorMessage;	//For Global Error Messages

$sVar_reDate = "[0-9][0-9][0-9][0-9][-][0-9][0-9][-][0-9][0-9]";
$sVar_reTime = "[0-9][0-9][:][0-9][0-9][:][0-9][0-9]";
$sVar_reDateTime = $sVar_reDate . "[ ]" . $sVar_reTime;
$bVar_errorStatus = false;
$sVar_errorMessage = '';

$iVar_timestampChange = 0;
if ($iVar_hourDifference != 0)
	{
	if ($iVar_hourDifference > -13 && $iVar_hourDifference < 13)
		{
		$iVar_timestampChange = ($iVar_hourDifference * 60 * 60);
		}
	}

switch ($iVar_type)
	{
	case 0:
		if (ereg("^" . $sVar_reDateTime .'$',$sVar_sqlDateTime))
			{
			$aVar_dt = explode(' ', $sVar_sqlDateTime);
			$aVar_d = explode('-', $aVar_dt[0]);
			$aVar_t = explode(':', $aVar_dt[1]);
			$iVar_unixTimestamp = Mktime($aVar_t[0],$aVar_t[1],$aVar_t[2],$aVar_d[1],$aVar_d[2],$aVar_d[0]) + $iVar_timestampChange;
			}
		else
			{
			$bVar_errorStatus = true;
			$sVar_errorMessage .= 'Date and Time Format is incorrect';
			}
		break;
	case 1:
		if (ereg("^" . $sVar_reDate .'$',$sVar_sqlDateTime))
			{
			$aVar_d = explode('-', $sVar_sqlDateTime);
			$iVar_unixTimestamp = Mktime(0,0,0,$aVar_d[1],$aVar_d[2],$aVar_d[0]) + $iVar_timestampChange;
			}
		else
			{
			$bVar_errorStatus = true;
			$sVar_errorMessage .= 'Date Format is incorrect';
			}
		break;
	case 2:
		if (ereg("^" . $sVar_reTime .'$',$sVar_sqlDateTime))
			{
			$aVar_t = explode(':', $sVar_sqlDateTime);
			$iVar_unixTimestamp = Mktime($aVar_t[0],$aVar_t[1],$aVar_t[2]) + $iVar_timestampChange;
			}
		else
			{
			$bVar_errorStatus = true;
			$sVar_errorMessage .= 'Time Format is incorrect';
			}
		break;
	}

if (!$bVar_errorStatus)
	{
	if (@date($sVar_format,$iVar_unixTimestamp))
		{
		$sVar_formattedSQLDateTime = date($sVar_format,$iVar_unixTimestamp);
		}
	else
		{
		$bVar_errorStatus = true;
		$sVar_errorMessage .= '<br>Cannot convert given date to Unix Timestamp';
		}
	}

if ($bVar_errorStatus)
	{
	$bVar_gErrorStatus = false;
	$sVar_gErrorMessage .= '<br>' . $sVar_errorMessage;
	$sVar_formattedSQLDateTime = $sVar_errorMessage;
	//Comment above line if you do not wish to see error message in place of the Date/Time
	}

return $sVar_formattedSQLDateTime;

}

echo formatSQLDateTime('2003-12-23 10:55:05', 0, 'd/m/Y - H:i:s');
echo '<br>';
echo formatSQLDateTime('2003-12-23', 1, 'jS F, Y');
echo '<br>';
echo formatSQLDateTime('10:55:05', 2, 'g:i a T');
echo '<br>';
echo formatSQLDateTime('2003-12-23 10:55:05', 0, 'd/m/Y - H:i:s', -3);
echo '<br>';
echo formatSQLDateTime('2003-12-23 10:55:05', 0, 'd/m/Y - H:i:s', 3);
echo '<br>';
echo formatSQLDateTime('10:55:05', 2, 'g:i a', 2);
echo '<br>';

?>

Comment and Contribute

Your comment has been submitted and is pending approval.

Author:
Jay Perez

Comment:



Comment:

(Maximum characters: 1200). You have characters left.