Dattime format problems when retrieved on FreeBSD server

I'm using MSSQL with PHP and this works fine on a Windows server.

When i move to a FreeBSD server, the date formatting is not working.

FreeBSD retrieves the date as: mon dd yyyy hh:mi:ss:mmmAM - and php's functions for formatting date fails.

I've tried using: Convert(varchar(10), Date, 103) AS Date, and the date is formatted fine - BUT sorting on date does NOT work.

Are there any way i can do changes to datetime behaviour on server side? I NEVER wants the date in mon dd yyyy hh:mi:ss:mmmAM. I don't need milliseconds, and i want 24h format - not AM/PM. Are there any settings on the SQL server for this?

Where are you doing your sorting? With T-SQL CONVERT-style affects sort:

ORDERBYConvert(nvarchar(30), OrderDate, 109)

|||

I'm not sure what you mean. I SELECT Convert(varchar(10), Date, 103) AS Date, and last sentence is ORDER BY Date DESC.

Anyway - i would prefer to fix this server-side if possible, so that i don't need to rewrite all queries adding convert functions etc...

|||If you want to ORDER BY the output of Convert(varchar(10), Date, 103) then you have to put that expression in the ODER BY clause not simply the column named Date.|||

I'll try that, but i don't think it will work, because it returns the date as a string, and thus sorting the strings from highest numbers, to lower, which does not not necessarily need to be correct(?)

Is this really the only reason to make it work?

Dattime format problems when retrieved on FreeBSD server

I'm using MSSQL with PHP and this works fine on a Windows server.

When i move to a FreeBSD server, the date formatting is not working.

FreeBSD retrieves the date as: mon dd yyyy hh:mi:ss:mmmAM - and php's functions for formatting date fails.

I've tried using: Convert(varchar(10), Date, 103) AS Date, and the date is formatted fine - BUT sorting on date does NOT work.

Are there any way i can do changes to datetime behaviour on server side? I NEVER wants the date in mon dd yyyy hh:mi:ss:mmmAM. I don't need milliseconds, and i want 24h format - not AM/PM. Are there any settings on the SQL server for this?

Where are you doing your sorting? With T-SQL CONVERT-style affects sort:

ORDERBYConvert(nvarchar(30), OrderDate, 109)

|||

I'm not sure what you mean. I SELECT Convert(varchar(10), Date, 103) AS Date, and last sentence is ORDER BY Date DESC.

Anyway - i would prefer to fix this server-side if possible, so that i don't need to rewrite all queries adding convert functions etc...

|||If you want to ORDER BY the output of Convert(varchar(10), Date, 103) then you have to put that expression in the ODER BY clause not simply the column named Date.|||

I'll try that, but i don't think it will work, because it returns the date as a string, and thus sorting the strings from highest numbers, to lower, which does not not necessarily need to be correct(?)

Is this really the only reason to make it work?

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