Re: Different MSSQL output date format from the same PHP script

Greetings, Alessandro.
In reply to Your message dated Monday, February 18, 2008, 19:36:39,

I have a Windows Server 2003 R2 SP2 box where MS SQL 2000 SP4 is
running.
I have a debian "sarge" box running apache 2.0.54-5sarge2 and
libapache2-mod-php4 4.3.10-22.
I have another debian "lenny" box running apache 2.2.8-1 and
libapache2-mod-php 5 5.2.5-2.

On the two servers I have the same php script that queries a database
on the SQL server. One of the queried colums holds a datetime, and I
have that the output format is different between the two servers:
lenny server: Jun 7 2007 12:00:00:000AM
sarge server: Thu Jun 7 00:00:00 2007 <- this is what I want

I did not use any interpolation function (like strtotime()) but the
outcome is different.
How can I have the same output on the lenny server? Is this a server
configuration issue or what else? I would like not to use any php
function because I want to migrate my scripts from the old server to
the new one with the minimum effort.

It is different in case of different intermediate layers between PHP and MS
SQL server.
I've had similar issue when dealing with dates from MSSQL.
After some reading and consultations, I'm end up with solution similar to one
posted above:

SELECT CONVERT(VARCHAR, u.[create_date], 126) [create_date]
FROM [tablename] u;

It will give You best possible datetime representation I ever saw from the MSSQL.
It is XML-style "YYYY-MM-DD'T'HH:MM" format, like "2008-02-14T16:00"

Then You may just strtotime() that column and deal with "normal" timestamp and
format it as You wish using regular PHP *date() functions.


--
Sincerely Yours, AnrDaemon <anrdaemon@xxxxxxxxxxx>

Sample:

public function execute($request, $DB = NULL)
{
$result = false;

if(class_exists('DB_common') && !$DB instanceof DB_common)
{
$this->setLastError(500, sprintf(LANG_DEAD_DB_EXPLAINED, 500, 'Database handler unknown'),
__FILE__, __LINE__-3, __FUNCTION__);
}
elseif(DB::isError($rc = $DB->getAll(MSSQL_LIST_CHARS, array($request))))
{
$this->setLastError(500, sprintf(LANG_DEAD_DB_EXPLAINED, $rc->getCode(), $rc->getUserInfo()),
__FILE__, __LINE__-3, __FUNCTION__);
}
else
{
$result = array();
foreach($rc as $char)
{
$char['create_date'] = strtotime($char['create_date']);
$char['login'] = strtotime($char['login']);
$char['logout'] = strtotime($char['logout']);
$result[] = $char;
}
}

return $result;
}

.

Relevant Pages