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