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.
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.