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
WHERE (prlc_startdate < {ts '2005-07-15 09:32:28'})
Format for timestamp jdbc escape syntax is ....
{ts 'yyyy-mm-dd hh:mm:ss.f...'}
Equivalent to
TIMESTAMP 'yyyy-mm-dd hh:mm:ss.f...'
The fractional portion of timestamp constants (.f...) can be omitted.
VALUES {ts '1999-01-09 20:11:11.123455'}
Check in google for "JDBC escape syntax", then the driver is
responsible for generating the correct syntax for any database u
connect to. So your query will look like.
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 < {ts
'2005-07-15 09:32:28'} AND prlc_type = 'A' ORDER BY
prlc_startdate