Need help in putting the single quote and constant timestamp in plsql

Question

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):

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

Script:

spool C:\output\params.txt
SET SERVEROUTPUT ON;
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

begin
  :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;
exit 

Answer 1

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;