Home » SQL & PL/SQL » SQL & PL/SQL » Using Date Variable vs. Hardcoding
Using Date Variable vs. Hardcoding [message #239102] Mon, 21 May 2007 08:54 Go to next message
a92926
Messages: 2
Registered: May 2007
Location: Pittsburgh
Junior Member
Hi. It seems that when I switch from a hardcoded date in my where clause to a variable that the sql runs forever. With the hardcoded value it run for approx. 5 minutes. Here's a simplified version of what I am trying to do.

START_DATE DATE := TO_DATE('01' || TO_CHAR(SYSDATE,'MMYYYY'),'DDMMYYYY');

SELECT * FROM INVOICES_ALL
WHERE
INVOICE_DATE >= START_DATE;

This above seems to never finish (I've killed the query after an hour).

However, if I hard the date it runs in like 5 minutes.

select * from invoices_all
where
invoice_date >= to_date('01052007','ddmmyyyy');
Re: Using Date Variable vs. Hardcoding [message #239107 is a reply to message #239102] Mon, 21 May 2007 08:58 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Try the following

SELECT * FROM INVOICES_ALL
WHERE
INVOICE_DATE >= trunc(sysdate,'mm');
Re: Using Date Variable vs. Hardcoding [message #239113 is a reply to message #239102] Mon, 21 May 2007 09:09 Go to previous messageGo to next message
a92926
Messages: 2
Registered: May 2007
Location: Pittsburgh
Junior Member
Works great! Thanks a million!
Re: Using Date Variable vs. Hardcoding [message #239114 is a reply to message #239102] Mon, 21 May 2007 09:09 Go to previous messageGo to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member

Or do the following...


monthyear varchar2(8) := '01' || to_char(sysdate,'MMYYYY');

START_DATE DATE := TO_DATE(monthyear,'DDMMYYYY');

SELECT * FROM INVOICES_ALL 
WHERE
INVOICE_DATE >= START_DATE;



Then, build an index on invoice_date.
Re: Using Date Variable vs. Hardcoding [message #239237 is a reply to message #239114] Mon, 21 May 2007 17:12 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Ronald Beck wrote on Mon, 21 May 2007 15:09

monthyear varchar2(8) := '01' || to_char(sysdate,'MMYYYY');

START_DATE DATE := TO_DATE(monthyear,'DDMMYYYY');


Isn't that the same as

start_date DATE := TRUNC(SYSDATE,'MM');

Re: Using Date Variable vs. Hardcoding [message #239508 is a reply to message #239237] Tue, 22 May 2007 09:34 Go to previous message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
Actually, yes, it is. However, I didn't realize that until AFTER I wrote my reply. In fact, I've added "start_date DATE := TRUNC(SYSDATE,'MM');" to my collection of handy Oracle hints as the best method to generate the 1st of the current month.

Ron
Previous Topic: Open a file of any type stored in long raw field.
Next Topic: Create view - execute a function where it inserts a record
Goto Forum:
  


Current Time: Fri Dec 09 10:08:10 CST 2016

Total time taken to generate the page: 0.05915 seconds