When doing an INSERT via PHP into a MSSQL DB, I get the following error:
mssql_query(): message: Cannot insert the value NULL into column 'wmu_startdate', table 'wmuPortal.dbo.tbl_projects'; column does not allow nulls. INSERT fails. (severity 16)
But the SQL string is
INSERT INTO tbl_projects
(startdate)
VALUES
(
(SELECT CAST('31-08-2016' as datetime))
)
The Query executes fine when run in Microsoft SQL Server Management Studio. Via the PHP Extension, the error above occurs. I already toggled mssql.datetimeconvert = Off #/On in php.ini.
The same error occurs without the SELECT CAST statement, ie. only a String as the value. The startdate column is of type datetime.
UPDATE
I now have a working query. But this still doesn't solve whatever causes the incorrect handling of dates by default...
Replacing the SELECT CAST Statement with
(SELECT CONVERT(DATETIME, '31-08-2016', 105))
Does the trick. Now: What setting do I have to alter in order to get the mssql/freetds extension to handle this automatically?
A known-good test server does not specify anything in /etc/freetds/locales.conf or other places. mssql.datetimeconvert = Off is set on both servers.
I also set other time/date related options in php.ini (copied from known-good server), without success. Is datetime handling affected by other system/environment variables?