Login or Register to Ask a Question and Join Our Community


Programming

How to put variable date from SQL Script

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Programming How to put variable date from SQL Script
# 1  
Old 06-10-2011
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..

Code:
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 use this variable...
Code:
date= `date '+%d-%b-%y' | tr '[a-z]' '[A-Z]'`

It should show like this...
Code:
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 '09-JUN-11 12.00.00 AM' and '09-JUN-11 11.59.59 PM' group by value;
spool off
quit


Thanks in advance.


Br,
Pinpe
# 2  
Old 06-10-2011
If I understand correctly, you don't need the external date command:


Code:
[...]
where 
  availableat 
between 
  trunc(sysdate)
and
  trunc(sysdate) + 1 - 1/24/60/60
;

# 3  
Old 06-10-2011
Hi,

Oracle offers the sysdate function that returns the current date. If the datatype of "availableat" is date you can rewrite your query:
Code:
select value,count(*) as totalcount from pmowner.pinpebasev where trunc(availableat) = trunc(sysdate)  group by value;

# 4  
Old 06-10-2011
Hi radoulov & cero,

Thanks for your prompt response. But I want to have a date range (i.e. between 0000H to 2000H of today's date).

Br,
Pinpe
# 5  
Old 06-10-2011
Quote:
Originally Posted by pinpe
But I want to have a date range (i.e. between 0000H to 2000H of today's date).
? What 0000H to 2000H means?

Code:
SQL> alter session set nls_date_format='DD-MON-RRRR HH:MI:SS AM';

Session altered.

SQL> select trunc(sysdate), trunc(sysdate) + 1 - 1/24/60/60 from dual;

TRUNC(SYSDATE)          TRUNC(SYSDATE)+1-1/24/6
----------------------- -----------------------
10-JUN-2011 12:00:00 AM 10-JUN-2011 11:59:59 PM

Last edited by radoulov; 06-10-2011 at 11:57 AM..
# 6  
Old 06-10-2011
Shifting to uppercase
I believe the point is moot since the other posters pointed out the sysdate command, but if you are using ksh, instead of doing this to make a variable uppercase:
Code:
date= `date '+%d-%b-%y' | tr '[a-z]' '[A-Z]'`

do this:

Code:
typeset -u date=`date '+%d-%b-%y'`

which does the same thing but saves some resources since a pipe and an external program
do not have to be used. typeset -u changes the variable's
value to uppercase.
# 7  
Old 06-10-2011
Quote:
Originally Posted by cero
Hi,

Oracle offers the sysdate function that returns the current date. If the datatype of "availableat" is date you can rewrite your query:
Code:
select value,count(*) as totalcount from pmowner.pinpebasev where trunc(availableat) = trunc(sysdate)  group by value;

Consider that if the column availableat is indexed (with a single column index or is in the leading part of a multi-column index) and there's no FBI on it, using the same function (trunc), your version of the query will perform a full table scan of the mowner.pinpebasev table (or underlying table, if mowner.pinpebasev is a view).
Last edited by radoulov; 06-10-2011 at 12:02 PM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Append date to sql*plus spool (log) file in shell script

SQL*Plus version : 11.2.0.4 OS : Oracle Linux 6.5 SQL*Plus is a client application to connect to oracle database. The log file for this tool is generated via spool command as shown below. I am trying to append date ( $dateString ) to spool file as shown below. $ cat test2.sh #!/bin/bash... (4 Replies)
Discussion started by: kraljic
4 Replies

2. Shell Programming and Scripting

Shell script variable $1 used with put command

I have the following script used, i am new to shell scripting. tryign to understand. in the put $BASE_FOLDER/$base_name holds which path. What does it mean by $1 second path in put command is it constructing this path: /user/hive/warehouse/stage.db/$1 what is $1 holding in above path. ... (2 Replies)
Discussion started by: cplusplus1
2 Replies

3. Shell Programming and Scripting

Passing variable from file to sql from script

Hi Friend, I have one file in which some number are mentioned and number of lines are vary every time And i need to pass that number to my sql command from script. Suppose i have file acc.txt 45456546456 45464564565 67854353454 67657612132 Number of records are vary every time.... (20 Replies)
Discussion started by: pallvi_mahajan
20 Replies

4. UNIX for Dummies Questions & Answers

SQL Script to use variable value from input file

Here is the requirement, When I run the "run file KSH (sql)", it should substitute '${pCW_Bgn_DT}' with 201120 and '${pCW_End_DT}' with 201124 Input File ---------- $ cat prevwk.dat 201124 20110711 run file KSH (sql) ------------------ In this file, I want to use the variables... (1 Reply)
Discussion started by: shanrice
1 Replies

5. Shell Programming and Scripting

How to insert variable date (monthly) from SQL script

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... (1 Reply)
Discussion started by: pinpe
1 Replies

6. Shell Programming and Scripting

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... (1 Reply)
Discussion started by: pinpe
1 Replies

7. UNIX for Dummies Questions & Answers

SQL loader script - ORACLE environmental variable

I am new in unix.. I am running a sql loader script where I have to specify the data file path but the file name contains spaces in it so giving error multiple arguments I have tried it with "" and '' but does n't work the command is : $ORACLE_HOME/bin/sqlldr... (1 Reply)
Discussion started by: Sandip Dey
1 Replies

8. Shell Programming and Scripting

Pass a variable to SQL script

Hi Guys, I like to pass a variable to a sql file in a unix script.. I tried a below code.. var=200903 db2 -vf test.sql 200903 test.sql is as below. select * from db2.users where quarter = $1; Please tell me where i go wrong.. Thanks in advance, Magesh (2 Replies)
Discussion started by: mac4rfree
2 Replies

9. Shell Programming and Scripting

Convertion of Date Format using SQL query in a shell script

When I write Select date_field from TableA fetch first row only I am getting the output as 09/25/2009. I want to get the output in the below format 2009-09-25 i.e., MM-DD-YYYY. Please help (7 Replies)
Discussion started by: dinesh1985
7 Replies

10. Shell Programming and Scripting

script variable within a sql query

I created a script to read a csv file with four columns. The script also saved values of each col in a arry. In the script, i connected to db try to run a query to pull out data baisc on the values from the csv file. select Num from tableName where Sec_Num in ('${isin}') /*isin is an arry... (1 Reply)
Discussion started by: Sherry_Run
1 Replies
Login or Register to Ask a Question