I have 2 servers, each running the exact same version of PHP (5.3.5-1ubuntu7.8). They are also the exact same version of Ubuntu (11.04). I have written a PHP script to connect to a SQL Server 2008 R2 database, select a datetime value, and print_r() it to the screen.
When I run the script on one server, the datetime is returned in this format: 2011-08-20 00:00:00.000. When I run it on the other server, using the exact same script pulling from the same database record, I get this format: Aug 20 2011 12:00:00.000AM.
My problem is that I cannot figure out where the difference is on my servers that is causing the discrepancy in my datetime formats. I have played around with the mssql.datetimeconvert setting in the php.ini files for both Apache and the CLI, to no avail.
Has anybody encountered this issue before and determined what causes it? Any thoughts are extremely appreciated before I decide to just rebuild the servers. Thanks!
Does PHP not have the ability to format datetime values in a specific format? This is likely to do with local regional settings on the operating system rather than anything else, but you can always request a specific format from SQL Server instead of relying on PHP or the O/S to format them the same way by coincidence.
SELECT CONVERT(CHAR(10), col, 120) + ' ' + CONVERT(CHAR(8), col, 108)
FROM dbo.table;
That said, PHP is probably the best place to do this, but I have to apologize - I'm not versed in PHP well enough to tell you how to do that (in C#, for example, you'd use .Format()).
From here:
http://php.net/manual/en/datetime.format.php
Looks like instead of just dumping the string to the screen you should be using
echo date_format($date_var_from_rs, 'Y-m-d H:i:s');
As for the default formats on your Ubuntu servers, this blog post might be useful as well:
http://ccollins.wordpress.com/2009/01/06/how-to-change-date-formats-on-ubuntu/