Date time trouble in JTDS.

21 Oct 2005 9:36 PM

tutm

Hi all,
I have data in the table myTable like following:

myTable_oid, myTable_Time
1, 2005-10-21 10:58:34.531

And I want to perform the SQL query in java code using JTDS, like:

SELECT myTable_oid, myTable_Time FROM MyTable
WHERE (myTable_oid=? AND myTable_Time>=?)
I use function getTimeImpl() to grant value for Date parameter, but
ervery time when I traced in SQL server (use SQL profiler), it
displayed:
SELECT myTable_oid, myTable_Time FROM MyTable
WHERE (myTable_oid=1 AND myTable_Time>=’Oct 21 2005 10:58AM’).

What I expected is it must be:
SELECT myTable_oid, myTable_Time FROM MyTable
WHERE (myTable_oid=1 AND myTable_Time>=’2005-10-21 10:58:34.531’).

Please help me to figure out. Thanks a lot!

--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Date-time-trouble-JTDS-ftopict265494.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=913005

21 Oct 2005 10:16 PM

Jerry Spivey

I don't know what JTDS is but can you create a stored procedure that accepts
an imput parameter of the data type date time and supply it

2005-10-21 10:58:34.531

i.e,
--NOT TESTED

CREATE PROC USP_MYTABLE
@MYTABLETIME DATETIME
AS
SELECT myTable_oid, myTable_Time
FROM MyTable
WHERE myTable_oid=1 AND myTable_Time>=@MYTABLETIME
GO

EXEC USP_MYTABLE '2005-10-21 10:58:34.531'

HTH

Jerry

Show quote

.."tutm" <UseLinkToEm***@www.dbforumz.com> wrote in message
news:913005_d18cb0369f04e137eb5a4ecd3288343c@dbforumz.com...
> Hi all,
> I have data in the table myTable like following:
>
> myTable_oid, myTable_Time
> 1, 2005-10-21 10:58:34.531
>
> And I want to perform the SQL query in java code using JTDS, like:
>
> SELECT myTable_oid, myTable_Time FROM MyTable
> WHERE (myTable_oid=? AND myTable_Time>=?)
> I use function getTimeImpl() to grant value for Date parameter, but
> ervery time when I traced in SQL server (use SQL profiler), it
> displayed:
> SELECT myTable_oid, myTable_Time FROM MyTable
> WHERE (myTable_oid=1 AND myTable_Time>='Oct 21 2005 10:58AM').
>
> What I expected is it must be:
> SELECT myTable_oid, myTable_Time FROM MyTable
> WHERE (myTable_oid=1 AND myTable_Time>='2005-10-21 10:58:34.531').
>
> Please help me to figure out. Thanks a lot!
>
> --
> Posted using the http://www.dbforumz.com interface, at author's request
> Articles individually checked for conformance to usenet standards
> Topic URL:
> http://www.dbforumz.com/Date-time-trouble-JTDS-ftopict265494.html
> Visit Topic URL to contact author (reg. req'd). Report abuse:
> http://www.dbforumz.com/eform.php?p=913005

23 Oct 2005 10:06 AM

Erland Sommarskog

tutm (UseLinkToEm***@www.dbforumz.com) writes:

> I use function getTimeImpl() to grant value for Date parameter, but
> ervery time when I traced in SQL server (use SQL profiler), it
> displayed:
> SELECT myTable_oid, myTable_Time FROM MyTable
> WHERE (myTable_oid=1 AND myTable_Time>='Oct 21 2005 10:58AM').
>
> What I expected is it must be:
> SELECT myTable_oid, myTable_Time FROM MyTable
> WHERE (myTable_oid=1 AND myTable_Time>='2005-10-21 10:58:34.531').

I believe this is how dates are displayed in Profiler. You could
test with a query that inserts data into a table to verify that the
seconds and milliseconds make it to the database.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp