How to insert variable date (monthly) from SQL script

Shell Programming and Scripting
1

Hi Guys,

Can someone please help me on adding/inserting a variable to an sql scipt? Basically I want to generate data on monthly (i.e. July 01, 2011 to July 31, 2011) basis. As shown below..

set head off;
set linesize 300;
set pagesize 200;
spool /opt/oracle/temp/output.txt
select value,count(*) as totalcount from pmowner.pinpebasev where availableat between '${date} 12.00.00 AM' and '${date} 11.59.59 PM' group by value;
spool off
quit

I want to insert something like this statement...

...where availableat between trunc(sysdate);

DESIRED OUTPUT FILE:

set head off;
set linesize 300;
set pagesize 200;
spool /opt/oracle/temp/output.txt
select value,count(*) as totalcount from pmowner.pinpebasev where availableat between '01-JUL-11 12.00.00 AM' and '31-JUL-11 11.59.59 PM' group by value;
spool off
quit

Thanks in advance.

Br,
Pinpe

2

man date and check the +FORMAT option