SQL server 2000 -> JDBC Date Time Format Problem

Thread view:

mukesh bhakta - 15 Jul 2005 02:42 GMT

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:odbc 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

Reply to this Message

Paul Tomblin - 15 Jul 2005 02:56 GMT

In a previous article, "mukesh bhakta" <mukesh_bhakta@hotmail.com> said:

> AND prlc_cust_id = 'CU00001030' AND prlc_startdate < '15/07/2005
>09:32:28 AM' AND prlc_type = 'A' ORDER BY prlc_startdate
[quoted text clipped - 8 lines]
>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.

Signature

Paul Tomblin <ptomblin@xcski.com> 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

Reply to this Message

mukesh bhakta - 15 Jul 2005 02:59 GMT

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

Reply to this Message

SQL server 2000 -> JDBC Date Time Format Problem

Free Magazines