PDO datetime format for MSSQL/dblib

问题:

MSSQL 2005 database has collation "German_Phonebook_BIN" (but that's not important). Connection to db is done via PDO and FreeTDS (using PHP under Debian Squeeze). When I try to select datetime values from a table I get results like:

Apr 1 2008 12:00:00:000

But I expect to get

2008-01-01 00:00:00

(Regard, that the time 00:00:00 is transformed into 12:00:00, don't know why 00:00=12:00???) There is no way for me to manipulate the SELECT statements (for doing a conversion with CONVERT). I found no option in PDO for setting a date format. SET DATEFORMAT and SET LANGUAGE before the query runs doesn't also affect this. Can anyone give a hint where this can be done (and only be done) in PDO? (Btw. PEAR::MBD2 returns datetime columns in the expected format, but MDB2 is horrible when it has to work with UTF-8 and MSSQL)

OK, some more information (shows only important snippets):

<?php
$this->_dsn = 'dblib:host=' . $this->_db['host'] . ';dbname=' . $this->_db['database'] . ';charset=UTF-8';
$this->_handle = new PDO($this->_dsn, $this->_db['user'], $this->_db['password']);
print_r($this->_handle->query("SELECT [date_column] FROM [some_table]"));

回答1:

check the setting in /etc/freetds/locales.conf or wherever FREETDSCONF points to - for an example see https://www.centos.org/modules/newbb/viewtopic.php?topic_id=29646.

Another option could be to use convert in your SQL statement...

回答2:

I find the best way to use PHP_PDO_DBLIB with SQL SRV is to store dates as datetime2(6) in the MS SQL SERVER DB. It seems to solve a lot of problems when using the symfony framework anyway.

回答3:

I had this problem, too, and found that for some reason when I let freetds apply the settings from freetds.conf (instead of just using the full hostname in my connector string), the dates appeared correctly.

For example, if I used:

$link = new PDO("dblib:host=myhost.myfulldomain.com;dbname=MYDB", $user, $pass);

... then it DIDN'T work as expected - the dates were wacky. But if I used:

$link = new PDO("dblib:host=myhost;dbname=MYDB", $user, $pass);

... then it DID work because it found "myhost" in my freetds.conf file.

My freetds.conf file:

#   $Id: freetds.conf,v 1.12 2007/12/25 06:02:36 jklowden Exp $
#
# This file is installed by FreeTDS if no file by the same 
# name is found in the installation directory.  
#
# For information about the layout of this file and its settings, 
# see the freetds.conf manpage "man freetds.conf".  

# Global settings are overridden by those in a database
# server specific section
[global]
        # TDS protocol version
;  tds version = 4.2

   # Whether to write a TDSDUMP file for diagnostic purposes
   # (setting this to /tmp is insecure on a multi-user system)
;  dump file = /tmp/freetds.log
;  debug flags = 0xffff

   # Command and connection timeouts
;  timeout = 10
;  connect timeout = 10

   # If you get out-of-memory errors, it may mean that your client
   # is trying to allocate a huge buffer for a TEXT field.  
   # Try setting 'text size' to a more reasonable limit 
   text size = 5242880

# A typical Sybase server
[egServer50]
   host = symachine.domain.com
   port = 5000
   tds version = 5.0

# My MS SQL server
[myhost]
   host = myhost.mydomain.com
   port = 1433
   tds version = 8.0