> 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 <[email protected]> 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. > >
