ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Nicholoz Koka Kiknadze <kikna...@gmail.com>
Subject Re: date doesn't include hours, minutes, seconds
Date Tue, 19 May 2009 10:26:12 GMT
> IBatis creates this select:
> ....
> to_date('2009-05-15 23.59.59', 'yyyy-mm-dd hh24.mi.ss')

Well, there's no to_date function in your XML, so iBatis can not create it.
It only prepares statement (ps) with

AND b.ins_time >= ?
AND b.ins_time <= ?

and calls ps.setDate

Could you re-check your xml files and re-check iBatis logs, I'm sure iBatis
is not 'smart' enough to add Oracle-specific to_date functions to generated
sql.



On Tue, May 19, 2009 at 10:15 AM, MichaelSK <n-brio@post.sk> wrote:

>
> 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.
>
>

Mime
View raw message