Open SQL: How to Compare Timestamps?

[detlev_beutner]

Hi there,

I have to build a SQL statement dependant on different parameters, so using prepared statements does not make sense. So comparing dates or timestamps cannot be done via ps.setDate(i, someDate).

With this, I have tried for example to build "SELECT ID FROM MYDB WHERE SOMEDATE >= '2004-05-01 00:00:00.00'" Running this, an OpenSQLException is thrown: "type check error: operands in comparison must be compatible".

Wow! I would say, these are as compatible as they can be... Why does OpenSQL doesn't think the same about this situation?

And how can I compare dates/timestamps without using prepared statements?

Questions over questions. Would be very nice if somebody could give me some hint...

Thanks in advance

Detlev

[Former Member]

Hi Detlev,

the syntax check is correct, you are comparing different types; time stamps are actually a separate type. Take a look at the <b>CONVERT DATE d TIME t INTO TIME STAMP tst TIME ZONE tz</b> command. I think this is what you're looking for.

Best wishes, harald

[Former Member]

Did you try using a bind variable and prep statement instead?

[Former Member]

To clarify - I saw you didn't want to use prep stmt initially, but there are so many benefits of using prep statements, so why invent the wheel?

For instance:

- It gives you a platform-independent way of executing your query from JDBC code ;

- General automatic benefits of prep statements - stmt compiled once and can be re-used many times.

etc. etc.

-Yuri