Home » SQL & PL/SQL » SQL & PL/SQL » Passing DATE to procedure (3 MERGED) (10g)
| Passing DATE to procedure (3 MERGED) [message #485842] |
Fri, 10 December 2010 10:21  |
sdlasiter
Messages: 36
Registered: November 2010
|
Member |
|
|
I'm struggling with this one and was hoping for input.
I have a procedure that accepts a date as an input parameter. This parameter is used in a select statement to match on a table column of date type.
Here's my questions:
1) Should the parameter be declared and passed in as a varchar2 and then converted using to_date() or declared as a date type? If it's declared as a date type what format should it be passed in as, ie. '01-MAY-2010' or '01-MAY-10' or etc.
2) When I run the sql query, knowing data exist, like "WHERE test_date = '22-NOV-10'" I get no results so I know I'm not matching the date correctly but if I use "WHERE test_date LIKE '22-NOV-10%'" I get the correct results. Trying to build a variable to match this, and use in a cursor, consistently gives me the 'non-numeric character was found...' error
3) What's the best approach to formatting the test_date input variable to match on the date type column.
All input is appreciated.
SL
|
|
|
|
|
|
|
|
| Re: Passing DATE to procedure [message #485847 is a reply to message #485846] |
Fri, 10 December 2010 10:33  |
sdlasiter
Messages: 36
Registered: November 2010
|
Member |
|
|
|
For now I will personally be entering the date to run the procedure. In the future I will be creating a front end interface for the user that takes the input parameter and calls the procedure.
|
|
|
|
| Re: Passing DATE to procedure (3 MERGED) [message #485852 is a reply to message #485842] |
Fri, 10 December 2010 11:19 |
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
sdlasiter wrote on Fri, 10 December 2010 16:21
1) Should the parameter be declared and passed in as a varchar2 and then converted using to_date() or declared as a date type?
date
sdlasiter wrote on Fri, 10 December 2010 16:21
If it's declared as a date type what format should it be passed in as, ie. '01-MAY-2010' or '01-MAY-10' or etc.
As long as you use the corresponding format mask in the to_date this is irrelevant. Though you should always use a 4 digit year.
sdlasiter wrote on Fri, 10 December 2010 16:21
2) When I run the sql query, knowing data exist, like "WHERE test_date = '22-NOV-10'" I get no results so I know I'm not matching the date correctly but if I use "WHERE test_date LIKE '22-NOV-10%'" I get the correct results. Trying to build a variable to match this, and use in a cursor, consistently gives me the 'non-numeric character was found...' error
Then the dates in the db probably have times. If you have a date and don't specify a time component it's time will be midnight. It won't match anything with the same date but different time. Use trunc or between to get around this.
sdlasiter wrote on Fri, 10 December 2010 16:21
3) What's the best approach to formatting the test_date input variable to match on the date type column.
See answer to 1 above.
|
|
|
|
|
|
| Re: Passing DATE to procedure (3 MERGED) [message #485854 is a reply to message #485853] |
Fri, 10 December 2010 11:41 |
 |
Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
In Oracle, dates are stored as dates not in any particular format. The formats are used for displaying the date data. You use to_char to convert a date to a varchar2 and to_date to convert a varchar2 to a date. With to_date you should always supply a format model that matches the character data that you are supplying. Otherwise, Oracle attempts to do an implicit conversion and may or may not get it right, depending on what the current nls_date_format happens to be. If it sees "12/11/10", without a format, it may think it is December 11, 2010 or the 12th day of November, 2010, or the year 2012, November 10th or October 11th or other combinations. You should write code that will always work, not just happen to work sometimes. You want to compare dates to dates as dates, not as characters, if you compare 01-nov-2010 to 31-jan-2010 as characters, it thinks 01-nov-2010 is before 31-jan-2010 because the first character 0 is lower than the first character 3. It is not so much important where you do the conversion from character to date, just so that it is done. In general it is a good practice to do it at the earliest point possible, so if your procedure is going to compare a date, then pass it a date to a date parameter. Please see the example below of proper ways to handle dates.
SCOTT@orcl_11gR2> create table test_tab
2 (id number,
3 test_date DATE)
4 /
Table created.
SCOTT@orcl_11gR2> insert into test_tab values (1, to_DATE ('01-MAY-2010', 'DD-MON-YYYY'))
2 /
1 row created.
SCOTT@orcl_11gR2> insert into test_tab values (2, to_DATE ('22-NOV-2010', 'DD-MON-YYYY'))
2 /
1 row created.
SCOTT@orcl_11gR2> create or replace procedure test_proc
2 (p_date in DATE)
3 as
4 begin
5 for r in
6 (select *
7 from test_tab
8 where test_date >= p_date
9 and test_date < p_date + 1)
10 loop
11 dbms_output.put_line (r.id || ' ' || r.test_date);
12 end loop;
13 end test_proc;
14 /
Procedure created.
SCOTT@orcl_11gR2> declare
2 v_date DATE := to_DATE ('22-NOV-2010', 'DD-MON-YYYY');
3 begin
4 test_proc (v_date);
5 end;
6 /
2 22-NOV-10
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2> exec test_proc (to_DATE ('01-MAY-2010', 'DD-MON-YYYY'))
1 01-MAY-10
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2>
[Updated on: Fri, 10 December 2010 11:49] Report message to a moderator |
|
|
|
|
|
| Re: Passing DATE to procedure (3 MERGED) [message #485859 is a reply to message #485855] |
Fri, 10 December 2010 13:24 |
sdlasiter
Messages: 36
Registered: November 2010
|
Member |
|
|
Thanks for the input Barbara and BlackSwan.
Strange thing now when I call my procedure with p_testproc('22-NOV-2010') with declaration of p_testproc(pi_testdate date) I get the error "ORA-01843: not a valid month" and in my capture of the error to a table, right after the error it says 10-DEC-10.
|
|
|
|
|
|
| Re: Passing DATE to procedure (3 MERGED) [message #485861 is a reply to message #485859] |
Fri, 10 December 2010 13:34 |
|
Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
sdlasiter wrote on Fri, 10 December 2010 11:24
Strange thing now when I call my procedure with p_testproc('22-NOV-2010') with declaration of p_testproc(pi_testdate date) I get the error "ORA-01843: not a valid month"
'22-NOV-2010' is a character string.
TO_DATE ('22-NOV-2010', 'DD-MON-YYYY') is a date.
You need to pass a date, not a character string, to your procedure:
p_testproc ('22-NOV-2010', 'DD-MON-YYYY')
CORRECTION: THE ABOVE LINE SHOULD HAVE BEEN:
p_testproc (TO_DATE ('22-NOV-2010', 'DD-MON-YYYY'))
as previously explained and demonstrated.
edit: added correction, previously accidentally omitted to_date
[Updated on: Fri, 10 December 2010 13:50] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Passing DATE to procedure (3 MERGED) [message #485867 is a reply to message #485866] |
Fri, 10 December 2010 13:56 |
sdlasiter
Messages: 36
Registered: November 2010
|
Member |
|
|
Thanks Barbara. I'm working hard on learning my PL/SQL and getting back up to speed in the development arena after years away. I greatly appreciate your approach, teach by example, with patience.
I'm not sure I understand why some members feel the need to throw in that little dig or negative remark when there is someone here humbly asking for assistance. Regardless, I really do appreciate the help.
SL
|
|
|
|
|
|
| Re: Passing DATE to procedure (3 MERGED) [message #485878 is a reply to message #485873] |
Fri, 10 December 2010 14:55 |
sdlasiter
Messages: 36
Registered: November 2010
|
Member |
|
|
Procedure is working by passing in date directly.
test_proc(pi_date date);
test_proc(TO_DATE('22-NOV-2010','DD-MON-YYYY'));
Date used in select now pulls correct rows:
lv_testdate := pi_date
SELECT *
FROM sometable
WHERE trunc(column_date) = lv_testdate
Thanks to all for the help.
|
|
|
|
| Re: Passing DATE to procedure (3 MERGED) [message #485881 is a reply to message #485878] |
Fri, 10 December 2010 15:15 |
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
As Blackswan pointed out earlier, applying trunc (or any function) to a database column will prevent oracle from using any indexes on that column.
A better way to do what you want is this:
SELECT *
FROM sometable
WHERE column_date BETWEEN trunc(lv_testdate)
AND trunc(lv_testdate) + 1 - (1/24/60/60) --gives 23:59:59
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu May 15 23:33:07 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
