Learn how easy it is to sync an existing GitHub or Google Code repo to a SourceForge project! See Demo

Close

#532 Wrong Date-Handling if prepareSQL=0

open
nobody
5
2012-08-15
2007-08-02
Conny Kreyßel
No

I try to update in my project from jtds 1.2 to 1.2.1.

I have a lot of testcases and I get everytime the same error, a "convert char-type to datetime-type error".

The german message is:
"Meldung 242, Ebene 16, Status 3, Zeile 1
Bei der Konvertierung eines char-Datentyps in einen datetime-Datentyp liegt der datetime-Wert außerhalb des gültigen Bereichs."

To isolate the problem a go back to 1.2 and all works fine, then I go into the sql profiler and I switched between the two versions and saw the problem.

In 1.2 all date-/timestamp-fields are submitted as "yyyyMMdd" or "yyyyMMdd hh:mm:ss.nnn". But in 1.2.1 the date are submitted as "yyyy-MM-dd" or "yyyy-MM-dd hh:mm:ss.nnn".

And the problem is that the sql-server will interpret this date-strings as "yyyyddMM" or "yyyyddMM hh:mm:ss.nnn".

Can anyone confirm this or show me my fault?!

Thanks

Driver settings:
sendStringParametersAsUnicode=false
prepareSQL=0

Enviroment:
Java 1.5.0_09-b03
SQL-Server 2005

Discussion

  • Conny Kreyßel
    Conny Kreyßel
    2007-08-02

     
    Attachments
  • Conny Kreyßel
    Conny Kreyßel
    2007-08-02

    Logged In: YES
    user_id=601335
    Originator: YES

    Added test case for the bug.
    File Added: jtdstest.zip

     
  • Logged In: YES
    user_id=641437
    Originator: NO

    This is a bug because the format yyyy-mm-dd does not work reliably with all locales e.g. German.
    Is there a reason why you are using prepareSQL=0? This is the slowest and least efficient way of preparing a statement.
    If you do not want the driver to use stored procedures or server side prepared statements I suggest you use prepareSQL=2 which should solve your problem.