SmallDateTime from MS-SQL not working in PHP

Question

I have a strange problem that when using PHP to connect to a MSSQL database using freetds the query returns no data.

I have two SQL queries:

SELECT TOP 100 *
FROM [TBL_Contact] AS C
LEFT JOIN [TBL_Address] AS A
    ON (C.[ContactID] = A.[ContactID])
;

and

SELECT TOP 100
    N.[NoteID],
    N.[NoteTypeID],
    N.[IsPrivate],
    CAST(N.[NoteText] AS TEXT) AS [NoteText],
    N.[ManageUserID],
    N.[DisplayDate],
    N.[CreateUserID],
    N.[CreateDateE],
    N.[EditUserID],
    N.[EditDate]
FROM [TBL_Note] AS N
LEFT JOIN [TBL_Contact_Note] AS C
    ON (C.[NoteID] = N.[NoteID])
;

When run directly on the MSSQL Server they both work as expected and return data. However when running them via PHP by wrapping them in a mssql_query() function call.

$result = mssql_query('
   SELECT TOP 100 *
   FROM [TBL_Contact] AS C
    LEFT JOIN [TBL_Address] AS A
        ON (C.[ContactID] = A.[ContactID])
    ;
');

$result1 = mssql_query('
    SELECT TOP 100
        N.[NoteID],
        N.[NoteTypeID],
        N.[IsPrivate],
        CAST(N.[NoteText] AS TEXT) AS [NoteText],
        N.[ManageUserID],
        N.[DisplayDate],
        N.[CreateUserID],
        N.[CreateDateE],
        N.[EditUserID],
        N.[EditDate]
    FROM [TBL_Note] AS N
    LEFT JOIN [TBL_Contact_Note] AS C
        ON (C.[NoteID] = N.[NoteID])
    ;
');

Then only the first one returns data.

I have experimented by removing fields from the query and have found that it is the smalldatetime field N.[DisplayDate] that is causing the issue. When I run the query with that field removed then the mssql_query() call works.

Is there data in the TBL_NOTE table? Could you please add some example data to your question and some expected query outputs. Also if you run the queries directly on the MSSQL Server do they work as expected? — AeroX
– AeroX, Commented May 14, 2014 at 9:47
Hiya yes there is data and the query worked fine on the server iteself — Liam
– Liam, Commented May 14, 2014 at 9:49
Instead editing your question why dont you post it as answer and accept it, which will be helpfull — krishna
– krishna, Commented May 14, 2014 at 9:51

Liam · Accepted Answer · 2014-05-15 10:59:56Z

1

Ok I've found out what the issue is there is a bug with PHP and the smalldatetime data type

There is a fix for this which involves disabling the mssql.datetimeconvert setting in the php.ini file.

The fix found on the web is to using ini_set to change the setting.

ini_set('mssql.datetimeconvert', 0);

answered May 15, 2014 at 10:59

Liam

5368 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

AeroX Over a year ago

If your own answer also fixed your problem you should be able to mark your own answer as accepted after the grace period is over. This then indicates that an answer to the problem was found

Collectives™ on Stack Overflow

SmallDateTime from MS-SQL not working in PHP

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related