How to set datetime format for SQL Server PHP driver

Question

In my PHP script, I want to add a date to a column (of type datetime) in MS SQL Server. I set up the connection following this blog post (php5-sybase, php5-odbc, freetds on Ubuntu 14.04/15.10): https://blog.thesysadmins.co.uk/ubuntu-server-connect-to-mssql-via-php.html

My problem is, using the exact same query in a php script as in SQL Management Studio, the query via PHP fails, because the date column is interpreted as NULL. The (abbreviated) query:

INSERT INTO tbl_test (mydate) VALUES (’20-3-2004′)
message: Cannot insert the value NULL into column ‘mydate’ column does not allow nulls. INSERT fails.

As I said, the exact same query runs fine in SQL Management Studio. Now, this query runs without errors:

INSERT INTO tbl_test (mydate) VALUES (’3-20-2004′)

Obviously, the datetime in the Query is interpreted as M-D-Y, but I can't find any option to change that. Surprisingly, it works under Ubuntu 15.10, but not on 14.04, using the same config for apache, php. freetds and php5-odbc use their default values.

Neither SQL Server settings (@@LANGUAGE = Germand, sp_helplanguage German dateformat = dmy) nor php.ini (iconv datetime formats) have M-D-Y configured. mssql.datetimeconvert = On makes no difference to being Off.

tl;dr How do I tell PHP the datetime format isn't MDY but DMY?

Answer 1

The root cause was neither PHP, nor freetds, or any *NIX system setting. The well-documented /etc/freetds/locales.conf for setting the datetime format works fine.

BUT FOR THE FREETDS DRIVER TO INTERPRET DATES IN THE SPECIFIED FORMAT, YOU MUST NOT ENCODE IT AS A CHARACTER STRING IN YOUR QUERY

As you can see above in my question, the value for the field of type datetime is enclosed in single quotes / ticks. This way, FreeTDS interprets the data as a String (varchar to be precise) and just passes it on the the SQL Server. Which is why the query fails, because the SQL-Server rightfully doesn't allow varchars to be inserted into datetime columns.

Why the exact same query worked on one version of Ubuntu, but not the other is beyond my comprehension.

tl;dr Dont use quotes around datetime values in your queries. Took me just 3 months to realise :)

Answer 2

As pointed out in the comments using convert() is a workaround. I am not an expert in MS SQL. I mostly use MySQL. However looking it up, MS SQL datetime uses the format yyyy-mm-dd hh:mm:ss

There is SET DATEFORMAT for setting the order of the month, day, and year date parts for interpreting date, smalldatetime, datetime, datetime2 and datetimeoffset character strings.

SET DATEFORMAT dmy; should perform the task you need