php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #54648 PDO::MSSQL forces format of datetime fields
Submitted: 2011-05-02 10:06 UTC Modified: 2014-10-25 04:46 UTC
Votes:20
Avg. Score:4.3 ± 0.7
Reproduced:19 of 19 (100.0%)
Same Version:3 (15.8%)
Same OS:17 (89.5%)
From: mail at tomsommer dot dk Assigned: ssufficool
Status: Closed Package: PDO DBlib
PHP Version: 5.3.6 OS: Linux
Private report: No CVE-ID:
 [2011-05-02 10:06 UTC] mail at tomsommer dot dk
Description:
------------
The PDO::MSSQL layer forces a format upon datetime fields, which cannot be altered. The MSSQL extension does not.

mssql.datetimeconvert apparently has no effect on PDO.

Test script:
---------------
ini_set('mssql.datetimeconvert', 0);

$sql = mssql_query("SELECT GETDATE()");
var_dump(mssql_fetch_assoc($sql));

var_dump($mssql->query("SELECT GETDATE()")->fetch());

Expected result:
----------------
array
  'computed' => string '2011-05-02 10:02:08' (length=19)

array
  'computed' => string '2011-05-02 10:02:08' (length=19)
  0 => string '2011-05-02 10:02:08' (length=19)


Actual result:
--------------
array
  'computed' => string '2011-05-02 10:02:08' (length=19)

array
  'computed' => string 'maj 02 2011 10:02:08' (length=20)
  0 => string 'maj 02 2011 10:02:08' (length=20)


Patches

PDO_DBLIB-force-format-of-datetime-fields (last revision 2011-11-15 13:23 UTC) by steven dot lambeth at gmx dot de)

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2011-05-02 10:07 UTC] tomsommer@php.net
-Summary: PDO forces format of datetime fields +Summary: PDO::MSSQL forces format of datetime fields
 [2011-08-14 02:09 UTC] ssufficool at gmail dot com
It seems the standard ANSI SQL date/time format should be YYYY-MM-DD HH:MM:SS.F

The PDO and MSSQL conversions drop the fraction. Unfortunately this cause major breakage if it were "standardized" since php does not convert fractional times.

IMHO, all PDO drivers should return the date/time & interval values in YYYY-MM-DD HH:MM:SS format.
 [2013-02-12 16:45 UTC] d dot vergini at gameprog dot it
workaround if using freetds library

edit (or create if not exists)
/etc/freetds/locales.conf

with the following 2 rows

[default]
date format = %Y-%m-%d %H:%M:%S.%z
 [2014-01-01 12:44 UTC] felipe@php.net
-Package: PDO related +Package: PDO DBlib
 [2014-10-25 04:46 UTC] ssufficool@php.net
-Assigned To: +Assigned To: ssufficool
 [2015-06-03 13:23 UTC] mirigoyen at icihomes dot com
I can confirm this is still an issue in PHP 5.6.5 and is causing major problems for us.
 [2016-02-29 16:08 UTC] ab@php.net
Automatic comment on behalf of ab
Revision: http://git.php.net/?p=php-src.git;a=commit;h=53c036b30bdabedf841fb8716a7cd722bbab09a3
Log: Fixed #54648 PDO::MSSQL forces format of datetime fields
 [2016-02-29 16:08 UTC] ab@php.net
-Status: Assigned +Status: Closed
 
PHP Copyright © 2001-2017 The PHP Group
All rights reserved.
Last updated: Sun Feb 26 21:01:35 2017 UTC