SQL server 2000 -> JDBC Date Time Format Problem

Discussion in 'Java' started by mukesh bhakta, Jul 15, 2005.

  1. Hi guys,

    We have a strange problem when Java talks to SQL Server 2000.

    The following query runs great when executed from Query Analyzer.

    SELECT prlc_plis_code, prlc_cust_id, prlc_startdate,
    prlc_enddate, prlc_type FROM PriceListCustomer WHERE 1 = 1
    AND prlc_cust_id = 'CU00001030' AND prlc_startdate < '15/07/2005
    09:32:28 AM' AND prlc_type = 'A' ORDER BY prlc_startdate

    But when executed through the Java code (using MS Jdbc:eek:dbc driver) we
    get the following exception

    <snip>
    EXCEPTION - java.sql.SQLException: [Microsoft][ODBC SQL Server
    Driver][SQL S
    erver]The conversion of a char data type to a datetime data type
    resulted in an
    out-of-range datetime value.
    at
    sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6958)
    at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7115)
    at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(JdbcOdbc.java:3111)
    at
    sun.jdbc.odbc.JdbcOdbcStatement.execute(JdbcOdbcStatement.java:338)
    at
    sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(JdbcOdbcStatement.java:2
    53)
    <snip>

    The strange thing is it runs fine on our production server but has
    problems on the dev server.

    We have compared the db settings on both the machines using

    select name ,alias, dateformat from syslanguages where langid =
    (select value from master..sysconfigures where comment = 'default
    language')

    which yields

    British British English dmy

    This only tells me that there is some setting in Tomcat/JDBC which
    needs attention.

    Any tips would be of great help.

    Cheers


    MB
     
    mukesh bhakta, Jul 15, 2005
    #1
    1. Advertising

  2. mukesh bhakta

    Paul Tomblin Guest

    In a previous article, "mukesh bhakta" <> said:
    > AND prlc_cust_id = 'CU00001030' AND prlc_startdate < '15/07/2005
    >09:32:28 AM' AND prlc_type = 'A' ORDER BY prlc_startdate
    >
    >But when executed through the Java code (using MS Jdbc:eek:dbc driver) we
    >get the following exception
    >
    ><snip>
    >EXCEPTION - java.sql.SQLException: [Microsoft][ODBC SQL Server
    >Driver][SQL S
    >erver]The conversion of a char data type to a datetime data type
    >resulted in an
    >out-of-range datetime value.


    Instead of trying to pass the timestamp as a character string, assign it
    to a variable of type java.sql.Timestamp, and bind it.


    --
    Paul Tomblin <> http://xcski.com/blogs/pt/
    The implication of the camel on the front of the Perl book is, I think, quite
    clear: Perl programmers permanently have the hump and are predisposed towards
    spitting. -- Jonathan Page
     
    Paul Tomblin, Jul 15, 2005
    #2
    1. Advertising

  3. Hi Paul,

    Thanks for the quick response.

    However, the case being we do not want to change anything in the code
    at the moment rather we want to take the existing production code base
    and replicate a test environment.

    Is there anything in the settings that we should check to confirm that
    both servers are on the same wavelength?

    Cheers

    MB
     
    mukesh bhakta, Jul 15, 2005
    #3
  4. mukesh bhakta

    wm.wragg

    Joined:
    Sep 5, 2006
    Messages:
    1
    I have the same problem. It works fine on one SQL Server instance but not on another. They both have the same langid etc... Did you have any success in solving this?
     
    wm.wragg, Sep 5, 2006
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?Z2F1cmF2?=

    Uploading Data From MS Acess 2000 ti SQL server 2000

    =?Utf-8?B?Z2F1cmF2?=, Jan 9, 2006, in forum: ASP .Net
    Replies:
    2
    Views:
    816
    Mary Chipman [MSFT]
    Jan 9, 2006
  2. Kevin Munro

    JDBC Drivers SQL Server 2000

    Kevin Munro, Oct 21, 2003, in forum: Java
    Replies:
    0
    Views:
    333
    Kevin Munro
    Oct 21, 2003
  3. NickName
    Replies:
    1
    Views:
    327
    Manish Pandit
    Oct 26, 2006
  4. Lew
    Replies:
    0
    Views:
    469
  5. Robin Lawrie
    Replies:
    18
    Views:
    790
    Mark Schupp
    Mar 1, 2005
Loading...

Share This Page