Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2005
    Posts
    33

    Unanswered: 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 help

  2. #2
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    1) 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:
    Code:
    UPDATE[mytable]
    SET mydatetimecolumn= CONVERT(DATETIME, '2007-01-04 19:14:48', 120)

  3. #3
    Join Date
    Feb 2005
    Posts
    33
    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:MMS") when selecting them.
    Is there any datetime format which MSSQL can interprete without a convert command ? I read somewhere that "YYYYMMDD HH:MMS" 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 ?

  4. #4
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    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.

  5. #5
    Join Date
    Feb 2005
    Posts
    33
    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!

  6. #6
    Join Date
    Feb 2005
    Posts
    33
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •