Home ORA-01756: quoted string not properly terminated, a part of my query it's failing

Questions

ORA-01756: quoted string not properly terminated, a part of my query it's failing

December 7, 2021

select 'BETWEEN TO_DATE(''' || to_char(CURRENT_DATE, 'dd/MM/yyyy 00:00:00') || ''', ''DD/MM/YYYY HH24:MI:SS'') AND TO_DATE(''' || to_char(CURRENT_DATE + 1, 'dd/MM/yyyy 00:00:00')

Is there something bad in this Query? It’s not the full query it’s just a part of it. I’ts throwing this error:

ORA-01756: quoted string not properly terminated

>Solution :

Use the q-quoting mechanism.

By the way, your code can be simplified to

select q'[between trunc(current_date) and trunc(current_date + 1)]' result
from dual;

No need to to_char and then to_date current_date; it already is DATE datatype, just remove (truncate to midnight) time component.

By the way #2, format mask you used is wrong; should be dd/mm/yyyy hh24:mi:ss (not dd/mm/yyyy 00:00:00)

If you insist (though, I don’t know why would you), then

select q'[between to_date(to_char(current_date    , 'dd/mm/yyyy hh24:mi:ss'), 'dd/mm/yyyy hh24:mi:ss')]' ||
       q'[    and to_date(to_char(current_date + 1, 'dd/mm/yyyy hh24:mi:ss'), 'dd/mm/yyyy hh24:mi:ss')]'
    as result 
from dual;

Does it work? Yes:

SQL> select q'[between to_date(to_char(current_date    , 'dd/mm/yyyy hh24:mi:ss'), 'dd/mm/yyyy hh24:mi:ss')]' ||
  2         q'[    and to_date(to_char(current_date + 1, 'dd/mm/yyyy hh24:mi:ss'), 'dd/mm/yyyy hh24:mi:ss')]'
  3      as result
  4  from dual;

RESULT
--------------------------------------------------------------------------------
between to_date(to_char(current_date    , 'dd/mm/yyyy hh24:mi:ss'), 'dd/mm/yyyy
hh24:mi:ss')    and to_date(to_char(current_date + 1, 'dd/mm/yyyy hh24:mi:ss'),
'dd/mm/yyyy hh24:mi:ss')


SQL>

Now copy/paste the result into another query and verify it:

SQL> select *
  2  from dual
  3  where sysdate
  4  -- this is the "result":
  5  between to_date(to_char(current_date    , 'dd/mm/yyyy hh24:mi:ss'), 'dd/mm/yyyy hh24:mi:ss')    and to_date(to_char(current_date + 1, 'dd/mm/yyyy hh24:mi:ss'), 'dd/mm/yyyy hh24:mi:ss')
  6  ;

D
-
X

SQL>

Didn’t fail, eh?