How to put variable date from SQL Script

Applications Programming
1

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 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...

date= `date '+%d-%b-%y' | tr '[a-z]' '[A-Z]'`

It should show like this...

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

If I understand correctly, you don't need the external date command:

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

Hi,

Oracle offers the sysdate function that returns the current date. If the datatype of "availableat" is date you can rewrite your query:

select value,count(*) as totalcount from pmowner.pinpebasev where trunc(availableat) = trunc(sysdate)  group by value;
4

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

? What 0000H to 2000H means?

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
6

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:

date= `date '+%d-%b-%y' | tr '[a-z]' '[A-Z]'`

do this:

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

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).

8

Hi radoulov,

I think this will work for me. But instead of today's date, I want the date to be yesterday's date. In this case, instead of June 10... it should be June 09, 2011.

Thanks so much mate!

Br,
Pinpe

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

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

Hi radoulov,

It's giving me an error as shown below.

SQL> select value,count(*) as totalcount from pmowner.pinpebasev where availableat between trunc(sysdate) - 1, trunc(sysdate)  - 1/24/60/60 group by value;
select value,count(*) as totalcount from pmowner.pinpebasev where availableat between trunc(sysdate) - 1, trunc(sysdate)  - 1/24/60/60 group by value
                                                                                                          *
ERROR at line 1:
ORA-00905: missing keyword


SQL>

Br,
Pinpe

11

It should be:

select 
  value,
  count(*) as totalcount 
from 
  pmowner.pinpebasev 
where 
  availableat 
between 
  trunc(sysdate) - 1 
and
  trunc(sysdate) - 1/24/60/60 
group by 
  value;
1 Like
12

PERFECT!!! Thanks so much mate! :b: :slight_smile:

Br,
Pinpe