Hi Lukas,
> About dates, I don't know if this is the best strategy, but this is what we
> do:
>
> with formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS")
>
> Oracle:
> "to_timestamp('" + formatter.format(date) + "','YYYY-MM-dd HH24:MI:SS.FF')"
>
> SQLServer:
> "convert(datetime,'" + formatter.format(date) + "',121)"
Looking at the JDBC specification, I found the following (section
13.4.2 of JDBC 4.0):
"
Data sources differ widely in the syntax they use for date, time, and timestamp
literals. The JDBC API supports ISO standard format for the syntax of
these literals,
using an escape clause that the driver translates to native syntax.
The escape syntax for date literals is:
{d 'yyyy-mm-dd'}
The driver will replace the escape clause with the equivalent native
representation.
For example, the driver might replace {d ’1999-02-28’} with
'28-FEB-99' if that is the
appropriate format for the underlying data source.
The escape syntax for TIME and TIMESTAMP literals are:
{t 'hh:mm:ss'}
{ts 'yyyy-mm-dd hh:mm:ss.f . . .'}
The fractional seconds (.f . . .) portion of the timestamp can be omitted.
"
I tried it on my SQLServer and Oracle DB:
select * from SomeTable
where SomeDateTime < {ts '2012-02-06 20:48:59'}
and it worked. Learning something new every day!
-Christopher
