ORA-01830: date format picture ends before converting entire input str 1

[codehead]

Hello All:

I am getting the following error message:

ORA-01830: date format picture ends before converting entire input string Error detected at position #:1706

when I run SQL like this simplified pseudo-code:

SELECT FIELD_AA, FIELD_BB
FROM (SELECT FIELD_A AS FIELD_AA, FIELD_B AS FIELD_BB
FROM TABLE_A, WHERE FIELD_A = 'SomeValue' AND FIELD_X IS NOT NULL
UNION
SELECT (FIELD_A AS FIELD_AA, FIELD_B AS FIELD_BB
FROM TABLE_A, WHERE FIELD_A = 'SomeValue' AND FIELD_Y IS NOT NULL)
WHERE FIELD_A BETWEEN TO_DATE('06/01/02', 'MM/DD/YY' ) AND TO_DATE('07/16/02', 'MM/DD/YY' )

The problem is using the BETWEEN statement with the UNION statement, because when I run the code without the UNION statement, it runs fine. I have also tried using < and > to frame the dates, but I still get the same error. Is there another way to get data in between dates without using the BETWEEN statement or the <> signs?

Thanks in advance!

 

[ThomVF]

Any chance you could post a copy of the actualy query, with and without the UNION ? =================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[sem]

This error normally means that you use wrong DATE format mask. Try to check whether FIELD_A compared against 'SomeValue' is also of date datatype. An example:

select to_date('01.01.01','dd.mm') from dual

causes the same error

 

[carp]

It looks like you have unbalanced parentheses.
I don't know if your actual code has this problem or not.

Also, you have a stray comma in your subqueries
(...FROM TABLE_A,)

Finally, which query does the final WHERE clause belong to? It LOOKS like you meant it to belong to the outer query, in which case it will fail because it has no FIELD_A to reference. Pull it into the subqueries and you should be OK. This will also speed up your query since there will be fewer rows to sort.
If you don't post your actual code, it is harder to help you troubleshoot it.

 

[codehead]

Hello All:

I removed the TO_DATE function, put the dates in as follows and it works now:

BETWEEN '01-JUN-02' AND '17-JUL-02'

Apparently it didn't like the TO_DATE function. Any thoughts?

Thanks.

 

[bppbme]

It would be helpful if you could post simplified test cases (perhaps cut and paste from sqlplus) showing your working and non-working queries.

Have a look at the following link to see some valuable advice on date comparisons.

http://asktom.oracle.com/pls/ask/f?...RITERIA:2858890655722,{between} and {to_date}

 

[stefanhei]

Hi.
If you use year in 2 digit format the formatpattern has to look like : TO_DATE('06/01/02', 'MM/DD/RR' )

Stefan

 

[fmorel]

Your &quot;where&quot; statement should filter values definied in the &quot;UNION&quot; subquery:

--> the subquery defines FIELD_AA and FIELD_BB in the result set.

(SELECT FIELD_A AS FIELD_AA, FIELD_B AS FIELD_BB
FROM TABLE_A, WHERE FIELD_A = 'SomeValue' AND FIELD_X IS NOT NULL
UNION
SELECT (FIELD_A AS FIELD_AA, FIELD_B AS FIELD_BB
FROM TABLE_A, WHERE FIELD_A = 'SomeValue' AND FIELD_Y IS NOT NULL)

--> so your &quot;where&quot; statement should be on FIELD_AA or FIELD_BB whereas it is on FIELD_A

 

[codehead]

Hello All:

I apologize....I meant to say &quot;when I run the code without the BETWEEN statement, it runs fine. Sorry for the confusion! Also, I apologize for the syntax typos - the code is correct in the real query.

Anyway, it runs o.k. without the TO_DATE function, so I'm going to leave it at that for now.

Thanks!