rabudde rabudde - 1 year ago 251
SQL Question

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

statements (for doing a conversion with
). I found no option in PDO for setting a date format.
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):

$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]"));

Answer Source

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...