Datetime problems

Hallo,

I have two different problems with datetime columns and MSSQL:

1)
Connecting via PHP 4.4.4 (Linux/Apache/freetds/mssql_connect) to MSSQL I receive a wrong month when selecting a datetime column: 00 is January and 11 December. E.g.:
select getdate()
2007-00-04 19:14:48

Selecting "convert(varchar(30), getdate(), 120)" returns the correct date:
2007-01-04 19:14:48
But I need that getdate() or selecting datetime columns without convert works correct, too.

2)
Connecting via PHP 4.4.4 (Windows Server 2003/ISAPI/MS-IIS 6.0/mssql_connect) to MSSQL and updating a datetime column it interchanges month and day, e.g.
update [mytable] set mydatetimecolumn="2007-01-04 19:14:48"
will set the datetime column to "2007-04-01 19:14:48".
I solved the problem with a "set dateformat ymd" before updating but I would prefer a permanent solution without the set command.

Thanks for any help1) This looks like a driver problem to me. Since converting the date to a string on SQL Server gives you the right date, nothing is wrong there. See if there are any know problems with the drivers and make sure you have the latest version.

2) De format in which the dates are handled on SQL server is determined in your server settings. Sending a string and hoping it will be alright is never a good idea (moving the databases to another server can get you in a lot of trouble that way). Always tell SQL Server in which format you are supplying the date by using the convert-function:
UPDATE[mytable]
SET mydatetimecolumn= CONVERT(DATETIME, '2007-01-04 19:14:48', 120)|||Thank you very much for your reply, Lexiflex.

2)
I need it in generic scripts where I don't know which column is a datetime field. And I don't really want to detect which type each column has before updating.
I thought that this format (ODBC canonical) cannot be misunderstood and that every SQL DBMS would interprete it correctly.
The strange thing is that I receive datetime columns in ODBC canonical ("YYYY-MM-DD HH:MM:SS") when selecting them.
Is there any datetime format which MSSQL can interprete without a convert command ? I read somewhere that "YYYYMMDD HH:MM:SS" could be. But I didn't found it in the CAST/CONVERT table. And I don't know how to convince MSSQL to return the datetime columns in this format by default.

Any suggestions ?|||The format "YYYYMMDD HH:MM: SS" seems to work and I sometimes use it when I need to use a date quickly in a development area.

I don't know how to convince MSSQL to return the datetime columns in this format by default.
You have to understand that sending a date is different from receiving one. When SQL Server returns a date it does so in a, to me, unknown way and it is the client application that formats it before presenting it to you (e.g. you can tell Query Analyzer how to show dates). So if you want to present it in a certain format you have to look a the settings of your client application.|||1)
The problem is still unsolved.
Does anybody have the same problem?
The problem seems to come from the PHP mssql support.
(I have some bigger problems with the odbc driver, too. So I cannot use it.)

Using odbc- or mssql- connect return different results
when selecting getdate() although both should use freetds i think:
PHP using odbc_connect (correct)
2007-02-05 16:25:25
PHP using mssql_connect (wrong)
2007-01-05 16:25:25

TSQL (freetds) tells me the correct date, too:
1> select getdate()
2> go
2007-02-05 16:25:25

Here more details of the system:
-----------
Apache/1.3.33 (Debian GNU/Linux)
PHP/4.4.4
mssql Library version 7.0
mssql.compatability_mode off
mssql.datetimeconvert off
ODBC library unixODBC
freetds v0.62.4 (TDS version: 8.0, unixodbc: yes)

Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
Desktop Engine on Windows NT 5.2 (Build 3790: Service Pack 1)

PLEASE HELP!|||1)
Finally we solved the problem with compiling FreeTDS using --enable-msdblib.

See also:
http://bugs.php.net/bug.php?id=22060
http://bugs.php.net/bug.php?id=32022