PDO datetime format for MSSQL/dblib PDO datetime format for MSSQL/dblib sql sql

PDO datetime format for MSSQL/dblib

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.

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