Question to_date()

pivert

New Member
I have a date-value in a characterfield, European style so it's "DD/MM/YYYY" with leading zeroes if needed. But I can't get my sql-statement working (sql statement is processed by an asp.net odbc program for small ad-hoc reports).

I've tried several options (part of the WHERE-clause):
TO_DATE(TO_CHAR(datedc, 'MM/DD/YYYY'))
TO_DATE(TO_CHAR(substr(datedc, 4,3) + substr(datedc, 1, 3) + substr(datedc, 7, 4)), 'MM/DD/YYYY'))
or whatever combination I could think of starting with a simple TO_DATE(datedc). But I keep getting errors.

This date (creation date) should be compared to ADD_MONTHS(SYSDATE, -2) so WHERE TO_DATE... > ADD_MONTHS...

The first part of the SQL works: SELECT datedc, TO_DATE(TO_CHAR(rsv6dc), 'DD/MM/YYYY') as createddate,... so the TO_DATE-statement works

But as soon as I try to add the WHERE-clause, I get errors like "error in format type", "error in date-value"

I thought this was going to be a 5 minute task but I've been struggling with this for 3 days now. Any ideas? I found knowledgebase.progress.com/articles/Article/000029661 and thought I hit the jackpot but this didn't work either.
 

pivert

New Member
Solved it. This sql goes through the whole WHERE statement. I added a CASE (see below)

CASE WHEN LENGTH(datedc) = 10 THEN
to_date( to_char(substr(datedc,7,4)) + '-' + to_char(substr(datedc,4,2)) + '-' + to_char(substr(datedc,1,2)))
END > add_months(sysdate - dayofmonth(sysdate) ,-2)

I hope this helps someone someday...
 
Top