Hello,
I’ve a problem with select that is created by IBatis. In my .xml file I’ve
the following statement:
SELECT b.id, b.id_credit, b.status, b.tag, b.ins_time, b.modif_time
FROM ws.vi_batchcompl b
<dynamic prepend="WHERE">
<isEqual prepend=" AND "
property="searchFilter.completionStatusType" compareValue="1">
decode(status, 'a', 1, 'c', 2, 'n', 3, 'x', 4, NULL) IN (1, 4)
</isEqual>
<isEqual prepend=" AND "
property="searchFilter.completionStatusType" compareValue="2">
decode(status, 'a', 1, 'c', 2, 'n', 3, 'x', 4, NULL) = 2
</isEqual>
<isEqual prepend=" AND "
property="searchFilter.completionStatusType" compareValue="3">
decode(status, 'a', 1, 'c', 2, 'n', 3, 'x', 4, NULL) = 3
</isEqual>
<isEqual prepend=" AND "
property="searchFilter.completionStatusType" compareValue="4">
decode(status, 'a', 1, 'c', 2, 'n', 3, 'x', 4, NULL) = 4
</isEqual>
<isNotNull prepend=" AND " property="completionInsTimeFrom">
<![CDATA[b.ins_time >= #completionInsTimeFrom#]]>
</isNotNull>
<isNotNull prepend=" AND " property="completionInsTimeTo">
<![CDATA[b.ins_time <= #completionInsTimeTo#]]>
</isNotNull>
</dynamic>
Variables completionInsTimeFrom and completionInsTimeTo have type
java.util.Date. IBatis creates this select:
SELECT b.id, b.id_credit, b.status, b.tag, b.ins_time, b.modif_time
FROM ws.vi_batchcompl b
WHERE decode(status, 'a', 1, 'c', 2, 'n', 3, 'x', 4, NULL) IN (1, 4)
AND b.ins_time >= to_date('2009-05-11', 'yyyy-mm-dd')
AND b.ins_time <= to_date('2009-05-15', 'yyyy-mm-dd')
The problem is that I want to compare the dates using also hours, minutes
and seconds. I want this select to look like this:
SELECT b.id, b.id_credit, b.status, b.tag, b.ins_time, b.modif_time
FROM ws.vi_batchcompl b
WHERE decode(status, 'a', 1, 'c', 2, 'n', 3, 'x', 4, NULL) IN (1, 4)
AND b.ins_time >= to_date('2009-05-11 00.00.00', 'yyyy-mm-dd hh24.mi.ss')
AND b.ins_time <= to_date('2009-05-15 23.59.59', 'yyyy-mm-dd hh24.mi.ss')
I cannot use java.util.Timestamp because there is an index on the column and
when I use Timestamp Oracle won’t use the index and but uses full scan. Can
you please help me how to solve this problem?
Michael
--
View this message in context:
http://www.nabble.com/date-doesn%27t-include-hours%2C-minutes%2C-seconds-tp23613367p23613367.html
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
