Login or Register to Ask a Question and Join Our Community


How to put variable date from SQL Script


 
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 10: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 11:02 AM..
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

Featured Tech Videos