Please help me in appending the Single quotes and constant timestamp to my query output. Given below is the expected output from my param file.

Note: Query is executed through sqlplus

Expected output in spool file(params.txt):

'15-DEC-2014 23:59:59'


spool C:\output\params.txt
variable from_date varchar2(30);
variable to_date varchar2(30);
set verify off 
set feedback off 
set trimspool on 
set linesize 1000
set term off 
set colsep , 
set pagesize 0
set heading on
set underline off

  :From_date := '&1';
  :To_date := '&2';
end; /

select to_date(:From_date,'dd-mon-yyyy') from Scehma.table1;
select to_date(:To_date, 'dd-mon-yyyy') from Scehma.table1;
exit 0;

To add single quotes in a string literal, just double it, or use the chr function.

E.g. 'hello '' world' represents the string hello ' world. 'hello '||char(39)||' world' is the same. '''' is a string literal representing a single single-quote.

To get your fixed end time, just concatenate it.

select ''''||to_date(:From_date,'dd-mon-yyyy')||'''' from Scehma.table1;
select ''''||to_date(:To_date,  'dd-mon-yyyy')||' 23:59:59''' from Scehma.table1;
  • Thanks a lot for your immediate respone, it is working as expected. – user61411 Dec 18 '14 at 12:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.