Question:

I have a field that is type number and is displayed like 200610. I need to turn it into a date field so the query can run off the system date. I tried using to_date and got “ORA-01861: literal does not match format string”. How can I convert that field so I can perform date comparisons with it, like one month before that field, 2 months before that field, etc…
And how can I also group that same field so it displays as “Oct – 06″?

Solution:

Convert every instance of a.my_date in the WHERE clause:

SELECT a.my_date, TO_DATE(a.my_date,’YYYYMM’) as my_month
FROM sch.tbl a
WHERE TO_DATE(a.my_date,’YYYYMM’)
BETWEEN add_months(to_date(a.my_date,’YYYYMM’),-1) AND TO_DATE(a.my_date,’YYYYMM’)
GROUP BY a.my_date