How can I add a string into the end of the other string in oracle mybatis sql?

Question

I am new to mybatis and I have a problem adding substring to the parameter I give to the sql query.

I am currently working on mybatis and I am trying to add a substring to the parameter I give to the sql query. I am using Oracle ojdbc14 with version 10.2.0.4.0.

This is what I expected from my trial:

AND EVT_OCCURDT BETWEEN TO_DATE('20171102 000000', 'YYYYMMDD HH24MISS') + 3/24 
AND TO_DATE('20171102 235959', 'YYYYMMDD HH24MISS') + 3/24

And what I did was:

<if test="evt_occurdt != null and !evt_occurdt.equalsIgnoreCase('')">
AND EVT_OCCURDT BETWEEN TO_DATE(#{evt_occurdt} || ' 000000', 'YYYYMMDD HH24MISS') + 3/24
AND TO_DATE(#{evt_occurdt} || ' 235959', 'YYYYMMDD HH24MISS') + 3/24
</if>

And my trial ended with java.sql.SQLException: ORA-01843. I guess there is a problem with my syntax, but I can't find the right syntax including the parameter.

UPDATE

I tried some other solution:

<if test="evt_occurdt != null and !evt_occurdt.equalsIgnoreCase('')">
AND EVT_OCCURDT BETWEEN TO_DATE(TO_CHAR(#{evt_occurdt}, 'YYYYMMDD') || ' 000000', 'YYYYMMDD HH24MISS') + 3/24 
AND TO_DATE(TO_CHAR{#(evt_occurdt}, 'YYYYMMDD') || ' 235959', 'YYYYMMDD HH24MISS') + 3/24
</if>

It still didn't work, with error code ORA-01481

Does your query works executing it after replace the parameters? ORA-00911 is "invalid character", so try to execute the query and let see what happens — Evgeni Enchev, Jan 24 '19 at 06:56
@EvgeniEnchev Yes, it works fine if I replace the parameters, and by the way, I recognized that the error code was still ORA-01481, with the UPDATE case. — 박주언, Jan 24 '19 at 07:02
Sorry, don't know but take a look at [this](https://stackoverflow.com/questions/33687413/sql-date-conversion-results-to-invalid-number-format-model-parameter) — Evgeni Enchev, Jan 24 '19 at 07:07

score 0 · Accepted Answer · answered Jan 24 '19 at 08:46

I finally solved the problem. Here is the solution:

<if test="evt_occurdt != null and !evt_occurdt.equalsIgnoreCase('')">
AND trunc(EVT_OCCURDT) = TO_DATE(TO_CHAR(#{evt_occurdt}), 'YYYY-MM-DD')
</if>

Just by using trunc() to EVT_OCCURDT rather than trying to append some string solved the problem.

How can I add a string into the end of the other string in oracle mybatis sql?

1 Answers1