[freetds] Dates fetched from MSSQL are off by 1 month

ZIGLIO, Frediano, VF-IT Frediano.Ziglio at vodafone.com
Fri Nov 17 06:16:49 EST 2006


> 
> > From: Ole Christian Schroder
> > Sent: Thursday, November 16, 2006 10:43 AM
> > 
> > More specifically the
> > month is adjusted by one, thus november becomes october (or more
> > correctly, 11 becomes october).
> > 
> > The date is entered and fetched from the database with the same
> > application. It appears that the date is correctly stored in the
> > db, but adjusted when it is read, thus the application sees the
> > wrong date.
> 
> Exactly: "the date is correctly stored in the db, but 
> adjusted when it is read".  It is *not* adjusted by FreeTDS.  
> FreeTDS passes the data from the server to the client.  
> Verbatim.  That's its job.  Anything else[1] a bug.  
> 
> You can verify you're getting the same information with both 
> versions of FreeTDS using, say, tsql on both machines.  
> 
> The only function I can think of that yields the month as a 
> number in db-lib (the library used by the PHP mssql 
> extension) is dbdatecrack(). FreeTDS adheres to Sybase's 
> definition of this function; there is no conditional code 
> based on --enable-msdblib.  That's arguably a bug, because 
> Microsoft and Sybase define the range of DBDATEREC::month 
> differently.  Sybase uses [0-11], Microsoft [1-12].  
> 

PHP use dbdatecrack to compute "PHP date". From 0.63 you have something like

        if (dbproc->msdblib) {
                ++di->datemonth;
                ++di->datedweek;
        }

to adjust date (previously it was a #ifdef/#else/#endif). dbproc->msdblib is copied from a tdsdbopen parameter (which is used when you call dbopen).
See http://cvs.php.net/viewvc.cgi/php-src/ext/mssql/php_mssql.c?view=markup
A solution is to add a "#define MSDBLIB 1" as the first line.

> > <--
> > ...under linux the driver for mssql is SYBASE alias, and 
> FreeTDS is set
> > to work with SYBASE by default. The problem is that sybase counts
> > months from 0 to 11, and mssql from 1 to 12. that´s the cause of
> > that strange one month less. To correct this, u have to compile
> > FreeTDS with a "--enable-msdblib".  -->
> 
> That might do the trick.  It may be that PHP has conditional 
> code, and that unless you define --enable-msdblib, it assumes 
> you're using a Sybase server, meaning that when it calls 
> dbdatecrack() and sees DBDATEREC::month of 11, it thinks that 
> means "December".  
> 
> That hypothesis fits the facts in the opposite direction 
> AIUI.  But it's worth trying.  Let us know.  
> 
> > On RHE4 we are using freetds-0.63-1.2.el4.rf and on the RH9 we are
> > using freetds-0.62-9
> 
> BTW we released 0.64 some six months ago.  As long as you're 
> upgrading, you might want to see if you can get that version.  
> 
> --jkl
> [1] apart from character set conversion, of course. 
> 

freddy77



More information about the FreeTDS mailing list