MONTHS_BETWEEN function in where clause 1

[S3066]

Greetings Group,

I created a MONTHS_BETWEEN expression and am trying to incorporate this function in the "WHERE" clause of my query.

Has anyone tried this? I'm experiencing an error:

SQL error. Stmt #: 4353 Error Position: 0 Return: 1840 - ORA-01840: input value not long enough for date format.

Perhaps Oracle isn't equipped to handle such a function in the WHERE clause.

 

[karluk]

There is a problem with the format of the date in your expression, but of course no one will be able to spot the error unless you are willing to share the expression that is generating the error.

 

[S3066]

MONTHS_BETWEEN(TO_DATE3,'YYYY-MM-DD'),(TO_DATE( A.BIRTHDATE,'YYYY-MM-DD')))/12


--> :3 is a date prompt that user enters.

Division by 12 at very end to restate months in terms of years.

If you guys have any insight, much thanks.

 

[karluk]

This expression doesn't generate an error if :3 and A.BIRTHDATE are replaced by character strings in the YYYY-MM-DD format. That means one or the other of the two must contain incorrectly formated data.

The first step is to figure out which of the two is the cause. Can you do a query like

select TO_DATE( A.BIRTHDATE,'YYYY-MM-DD') from your_table a;

That should tell you whether there are any format problems in the birthdate column. If there aren't, you must be entering the replacement variable incorrectly, or maybe processing it incorrectly.

 

[S3066]

Thanks for the tip.

It turns out, for whatever reason, when I try that SELECT on both my date fields, even with the

TO_DATE(A.BIRTHDATE, 'YYYY-MM-DD), I end up getting, for example, 02-FEB-02, not YYYY-MM-DD.

I did the select on the prompt as well. Same result.

Yuck. It appears the Oracle processor isn't taking my TO_DATE properly in the select statement. The processor doesn't seem to care about it.

 

[karluk]

Most likely birthdate is already defined to be a date column, so you don't need to apply the to_date function on it. To_date is only needed if you are converting a character expression to a date.

Try using the following expression. It will probably work.

MONTHS_BETWEEN(TO_DATE3,'YYYY-MM-DD'), A.BIRTHDATE)/12

 

[karluk]

Please ignore my previous post. If you are getting to_date(birthdate) to return dates in the dd-mon-yy format, it indicates that your to_date function is working correctly. A problem in the birthdate column should have resulted in a repeat of your ORA-01840 error.

Can you also check the replacement value for :3? What happens when you run

select TO_DATE3,'YYYY-MM-DD') from dual;

 

[S3066]

With the prompt enclosed in the TO_DATE, I get the same result (DD-MMM-YY) from the select statement.

So, you're saying if I have a TO_DATE(<date value>, 'YYYY-MM-DD') in a select, and the result is DD-MMM-YY, this result is expected?

If so, I suppose I'll consider this problem a mystery.

 

[karluk]

No, it's no mystery. You are just misunderstanding what TO_DATE does. It converts a character string to Oracle's internal format for storing dates. Then, when the result is displayed by your select statement, Oracle automatically converts it back to a string. This time it uses whatever the default date format is set to for your session. Usually the default is dd-mon-yy, and that's what it is for you. Schematically it goes

10 byte character string (yyyy-mm-dd format) --> Oracle internal date (non-displayable) --> character string in default display format (usually dd-mon-yy)

However, back to the original problem. If you can select both :3 and birthdate without generating the ORA-01840 error, you don't seem to have any formatting problems. Is it possible this was a one-time error caused by inadvertently entering a bad date string for :3? Do you still get the error when you try your original script?

 

[S3066]

No, sorry to say I don't think this is a one-time error due to bad data entry.

I'm guessing I can enter for the :3 prompt, say I want Sept. 1, 2002 --> then I would enter 090202. I'm guessing this value is OK.

The :3 prompt is defined as a 'Date' type prompt.

Error still occurs.

Thanks for the insights, anyway. Appreciate it.

 

[karluk]

I think that's your problem. You are entering 090202 instead of 2002-09-01. If you specify a format of yyyy-mm-dd for :3, then Oracle expects you to enter your date in exactly this format, hyphens included. That also explains the error - the entered string is not long enough for the format you specified. It needs to be 10 bytes and you only entered 6.

 

[S3066]

Yeah, I had tried that idea at the beginning --> typing in Sept. 1, 2002 as 2002-09-01 into my prompt. Still same error.

As always, thanks for your time. I'll chuck this issue up to something funky in how the Oracle processor is handling the dates behind the scenes in the WHERE clause.