PL/SQL treating date differently in SQL hard code

Question

I developed a simple query to place in a pl/sql procedure. Through Toad I can pass in my two variables and it preforms perfectly, but for some reason when I place it in the procedure, it fails. I have tried numerous techniques but the only solution is to hard code the date, which defeats the whole purpose.

Inputs

DECLARE
   D_PROJECT_ID  NUMBER := 32311;
   END_DATE  DATE := '31-JAN-2015';
   ETC_BDLB NUMBER := 0;
   ETC_BDEC NUMBER := 0;
   ETC_BDLC NUMBER := 0;

Problem child, the first option is the only one which will work.

and to_date(b2.attribute2,'DD-MON-YYYY') <= '31-JAN-2015'); RETURNS $$$
                                          and to_date(b2.attribute2,'DD-MON-YYYY') <= END_DATE); --RETURNS 0  END_DATE VARCHAR2(11)
                                          and to_date(b2.attribute2,'DD-MON-YYYY') <= END_DATE); --RETURNS 0 END DATE AS DATE
                                          and to_date(b2.attribute2,'DD-MON-YYYY') <= TRUNC(END_DATE)); --RETURNS 0
                                          and to_date(b2.attribute2,'DD-MON-YYYY') <= TO_DATE(END_DATE, 'DD-MON-YY'));  --RETURNS 0
and to_date(b2.attribute2,'DD-MON-YYYY') <= TO_DATE(END_DATE, 'DD-MON-YYYY'));  --RETURNS 0

DEBUG OUTPUT:

[--- 1 ---]

[1] D_PROJECT_ID = 32311
[1] END_DATE = 31-JAN-15
[1] ETC_BDLB = 0
[1] ETC_BDEC = 0
[1] ETC_BDLC = 0

I will try any options.

Allready tried to assign a date explicitly casted in the declare part: END_DATE DATE := `to_date( '31-01-2015', DD-MM-YYYY);`, so that you can be sure `END_DATE`contains the desired date? — evilive, Mar 10 '15 at 16:48
I am assuming the second parameter should have single quotes. It gave me the same results. I can tell the date is correct based upon the variable print statements. — user2502794, Mar 10 '15 at 17:11
What are your `attribute2` values? What is your NLS_DATE_FORMAT? — Alex Poole, Mar 10 '15 at 17:25
attribute2 stores the date I am comparing, it is a varchar field in the table. The table is a seeded oracle table. — user2502794, Mar 10 '15 at 17:36

score 1 · Answer 1 · answered Mar 10 '15 at 16:54

1

Please try:

DECLARE
  END_DATE  DATE;
...
BEGIN
  END_DATE  := TO_DATE ('31-JAN-2015');
...
END ;

answered Mar 10 '15 at 16:54

Patrick

902
1
5
18

Can you Please provide some examples? select to_date(b2.attribute2) from b2; – Patrick Mar 10 '15 at 17:51
attribute2 contains data like this:30-JUN-2013 31-JUL-2013 31-AUG-2013 30-SEP-2013 31-OCT-2013 30-NOV-2013 31-DEC-2013 31-JAN-2014 28-FEB-2014 31-MAR-2014 – user2502794 Mar 10 '15 at 19:16
Are you aware of the fact, that a date not only contains day, month, year values, but the hours, minutes and seconds, too? If you create a new date with just providing some of these values, '31-JAN-2015' <= '31-JAN-2015' is not always true. 1. as already described, please ensure to set dates with to_date(string, format_string) 2. check the complete date value to anlyze your code, use to_char(date_value, format_string) a = '31-JAN-2015', b = '31-JAN-2015' ?: a <= b to_char(a,'YYYY-MM-DD-HH24-MM-SS') = '2015-01-31-09-15-44' to_char(b,'YYYY-MM-DD-HH24-MM-SS') = '2015-01-31-06-20-22' a > b – Patrick Mar 10 '15 at 20:25

score 1 · Answer 2 · answered Mar 10 '15 at 18:01

1

For a DATE literal you have to use this format (only)

DATE '2015-01-31'

Time fractions are not possible.

answered Mar 10 '15 at 18:01

Wernfried Domscheit

54,457
9
76
110

score 1 · Answer 3 · edited May 23 '17 at 11:59

Always remember these 2 basic rules when handling dates -

Never assign a string to a DATE variable and expect it to work
Never compare a string variable with a DATE variable.

You might be able to make it work sometimes but this practice should never go into production code. It's a pain for the DBA in the long run.

In the code above, both these rules are broken.

END_DATE  DATE := '31-JAN-2015';

The left side is a variable of type DATE and the right side is a string (Yes DD-MON-YYYY doesn't make it a date, it is still a string). This should have been

END_DATE  DATE := TO_DATE('31-JAN-2015', 'DD-MON-YYYY');

Also,

to_date(b2.attribute2,'DD-MON-YYYY') <= '31-JAN-2015'

This is a comparison between a DATE on the left and a string on the right. Should've been

to_date(b2.attribute2,'DD-MON-YYYY') <= to_date('31-JAN-2015','DD-MON-YYYY);

When you pass in parameters into a procedure, you might pass a VARCHAR2 in an agreed upon format, but before assigning it to a DATE variable though, convert it into DATE using the TO_DATE function.

Oracle internally stores dates as 7bytes or 8bytes depending on the implementation. See this. So it doesn't make sense to compare a string that says '31-JAN-2015' to a date type that probably has these 8 bytes - [223,7,1,31,0,0,0,0]. You would first need to convert that string into a date type with that same format of 8 bytes (or 7) and then compare them.

There is another parameter that comes into play here that is NLS_DATE_FORMAT. The only reason this exists is that whenever you compare a string and a date; OR try to assign a string into a date, before it returns a datatype mismatch error, oracle will do you a favor and check to see if that string follows the format specified in this parameter. If it does, then oracle will do the to_date behind the scenes.

score 1 · Answer 4 · answered Mar 11 '15 at 14:49

1

While I would not recommend my solution, I changed the input parameter to a varchar2 instead of a date. The date in the database is being stored as a varchar2 as well, and both variables are going through a to_date function before the comparison.

answered Mar 11 '15 at 14:49

user2502794

127
2
15

PL/SQL treating date differently in SQL hard code

4 Answers4