PDO insert into MSSQL datetime swaps day and month

Question

When I try to insert a record with date/time into MSSQL 2008 datetime field like this

$pQuery = "INSERT INTO myTable (myDate) VALUES (:my_date)";
$ps = $pdo->prepare($pQuery);
$ps->bindValue("my_date", date("Y-m-d H:i:s",strtotime('01.07.2013')), PDO::PARAM_STR);
$ps->execute();

with a string '01.07.2013' (DD.MM.YYYY) representing July 1st 2013, I end up with a value of '2013-01-07 00:00:00' in the table which represents January 7th 2013.

If the day is bigger than 12, insert fails with PDOException "SQLSTATE[22007]: [Microsoft][SQL Server Native Client 11.0][SQL Server]The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value".

When I do exactly the same to the datetime2 field, it is inserted correctly. Anyone has an idea how to circumvent this? Changing the datatype from datetime to datetime2 is not an option. Thank you.

Answer 1

I have found out elsewhere that using ISO format in date() function inserts the dates correctly regardless of the underlying database field type (datetime, datetime2, smalldatetime). So to avoid the problem in the question use

date("Ymd H:i:s")

when inserting dates to MS SQL database.