Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: How to put date range from a perl & sql script
Top Forums Shell Programming and Scripting How to put date range from a perl & sql script Post 302543159 by pinpe on Friday 29th of July 2011 07:42:00 PM
Old 07-29-2011
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..


Code:
#!/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...

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

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

perl script dies altogether when I put an & after it

ignore (2 Replies)
Discussion started by: hairytorus
2 Replies

2. Shell Programming and Scripting

Perl code to extract data from the range of date

Hi All, I'm still a newbie in perl programming. I have a data below say in test.tmp The output in test.tmp will be the same data as above sample in test.tmp . So after i get all the 4th column data within the range of month and year i need, then i will use the foreach () code to execute... (1 Reply)
Discussion started by: miskin
1 Replies

3. Shell Programming and Scripting

grep - date & time range

Hi, I need to search email files by date & time range in email files. The timezone is not important. Can someone plz advise how i can do this ? For e.g A user can specify only A single date A date range date & time range Below is part of the email file. (4 Replies)
Discussion started by: coolatt
4 Replies

4. Programming

How to put variable date from SQL Script

Hi Guys, Can someone please help me on adding/inserting a variable to an sql scipt? Basically I want to assign today's date. 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... (11 Replies)
Discussion started by: pinpe
11 Replies

5. Shell Programming and Scripting

Script on Date Range

Hi All, Can anybody help me out a Shell script which pulls the files based on date range Example ./test.sh start_date End_date (20110901 20110930) or ./test.sh ( if we don't provide any input) it should take sysdate-1 ( yesterdays date) it should have both conditions Plzz help me... (1 Reply)
Discussion started by: krux_rap
1 Replies

6. UNIX Desktop Questions & Answers

grep a range of time & date

how can i grep a range? i have a text file with the following text: result.log.00:2012/01/02 12:00:07.422 LOG STARTED HERE N6Kashya29MemoryShieldScheduler_AO_IMPLE, pid=8662/8658, config=(alertThreshold=10,alertLevel=0,killThreshold=7200,coreThreshold=0,full=1), deltaTime=0,... (1 Reply)
Discussion started by: boaz733
1 Replies

7. Shell Programming and Scripting

run script in time and date range

i need to run one script inside of other, and there is some terms - main script in scheduled in cron for everyday runing every 5min - i need to run /tmp/script2.sh after first 3 days in month - i need to run /tmp/script2.sh from 7-9AM, main script is runining all day all recommendations are... (1 Reply)
Discussion started by: waso
1 Replies

8. Shell Programming and Scripting

PERL, Date & Time issues

Hello All, This is my first script in PERL. Hence require your help in moving further. I have a script which should populate the values for Today, Yesterday output. For which I use timeFrame as a variable to obtain the time in hrs:mm as 10:00. All I want is, I want my timeFrame to start... (4 Replies)
Discussion started by: sathyaonnuix
4 Replies

9. Shell Programming and Scripting

Perl script to allocate next available IP from a range

Hi I am looking to automate the process in PERL of allocating IP addresses from a set range of addresses (for example a /22 network 10.10.224.1 - 10.10.227.254) I am able to query the IP addresses that are already in use in the above range, which will produce me a list like this for example... (3 Replies)
Discussion started by: hcclnoodles
3 Replies

10. UNIX for Advanced & Expert Users

To pass one month range in sql script

Hi Guys, i am having .sql script which inserts data from one table to another table based on date condition, i need to pass range on based on how many number of months, for e.g set timing on; whenever sqlerror exit failure; spool myscript.log append accept start_date... (7 Replies)
Discussion started by: rohit_shinez
7 Replies

LEARN ABOUT CENTOS

pmdate

PMDATE(1)						      General Commands Manual							 PMDATE(1)

NAME

       pmdate - display an offset date

SYNOPSIS

       pmdate [ offset ... ] format

DESCRIPTION

       pmdate displays the current date and/or time, with an optional offset.

       An offset is specified with a leading sign (``+'' or ``-''), followed by an integer value, followed by one of the following ``scale'' spec-
       ifiers;

       S      seconds
       M      minutes
       H      hours
       d      days
       m      months
       y      years

       The output format follows the same rules as for date(1) and strftime(3).

       For example, the following will display the date a week ago as DDMMYYYY;
	       pmdate -7d %d%m%Y

PCP ENVIRONMENT

       Environment variables with the prefix PCP_ are used to parameterize the file and directory names used by PCP.  On  each	installation,  the
       file  /etc/pcp.conf contains the local values for these variables.  The $PCP_CONF variable may be used to specify an alternative configura-
       tion file, as described in pcp.conf(5).

SEE ALSO

       date(1), strftime(3), pcp.conf(5) and pcp.env(5).

Performance Co-Pilot							PCP								 PMDATE(1)
All times are GMT -4. The time now is 04:58 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy