How to put date range from a perl & sql script

Hi Guys,

Can someone please help me on adding/inserting a variable date to an sql scipt? Basically I want to assign a 7 days date range. As shown below..

#!/usr/bin/perl

use strict;
use Env qw(ORACLE_HOME);

my $SQLPLUS='/opt/oracle/product/10.1.0/db_1/bin/sqlplus -S system/password@PINPE';
my $outputdir="/var/opt/output/";
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time-(86400));
my $date=sprintf("%d%02d%02d",$year+1900,$mon+1,$mday);

SET SERVEROUTPUT OFF
SET HEADING OFF
SET PAGESIZE 0
SET LINESIZE 132
SET FEEDBACK OFF

select ','||b.number, c.studentid, a.classgroup, a.section
from pmowner.classdatat a, pmowner.classt b, pmowner.usedt c
where a.CLASSDATADBID = b.CLASSDATADBID and b.number = c.number
and c.at between to_date('${date}000000','yyyymmddhh24miss') and to_date('${date}235959','yyyymmddhh24miss');

I want to use this variable $date...

It should show like this...

SET SERVEROUTPUT OFF
SET HEADING OFF
SET PAGESIZE 0
SET LINESIZE 132
SET FEEDBACK OFF

select ','||b.number, c.studentid, a.classgroup, a.section
from pmowner.classdatat a, pmowner.classt b, pmowner.usedt c
where a.CLASSDATADBID = b.CLASSDATADBID and b.number = c.number
and c.at between to_date('20110722000000','yyyymmddhh24miss') and to_date('20110731235959','yyyymmddhh24miss');
quit;

Thanks in advance.

Br,
Pinpe

I am guessing, as I have not used oracle perl, that you need to explicitly concatenate, like '||' or '+'.

Between is not your friend, since it gets both end points. In dodging this, you added code, and in return, you might miss something between 235959 and 000000, if timestamps have factions. It snares the unwary all the time. Nobody has the guts to do "dt between a and b and dt != b" !

Time goes to zero by default.

Decide which end point is included and which is not. Usually one keeps the first instant of the day with the day, so:

and c.at >= to_date( ${date}, 'yyyymmdd' )
and c.at < ( 7 + to_date( ${date}, 'yyyymmdd' ) )