Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Java > SQL server 2000 -> JDBC Date Time Format Problem

Thread Tools

SQL server 2000 -> JDBC Date Time Format Problem

mukesh bhakta
Posts: n/a
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 Jdbcdbc driver) we
get the following exception

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.
sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc .java:695
at sun.jdbc.odbc.JdbcOdbc.standardError( :7115)
at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect( :3111)

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

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.



Reply With Quote
Paul Tomblin
Posts: n/a
In a previous article, "mukesh bhakta" <(E-Mail Removed)> 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 Jdbcdbc driver) we
>get the following exception
>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 <(E-Mail Removed)>
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 With Quote
mukesh bhakta
Posts: n/a
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?



Reply With Quote
wm.wragg wm.wragg is offline
Junior Member
Join Date: Sep 2006
Posts: 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?
Reply With Quote

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Re: java.sql.Date value truncated using JDBC in MS-SQL Server Lew Java 0 02-09-2007 01:21 AM
How to get data from a particular row with microsoft jdbc for sql server 2000? NickName Java 1 10-26-2006 10:32 PM
covert time from date Hour min sec format to epoch time i.e time since 1 jan 1970 in C Summu82 C Programming 5 06-07-2006 02:51 PM
Insert Date and Time in SQL Server 2000 using ASP Robin Lawrie ASP General 18 03-01-2005 03:37 PM
JDBC Drivers SQL Server 2000 Kevin Munro Java 0 10-21-2003 12:31 PM