How do you convert YYYY_MM to date in Oracle SQL?

Question

I have a column which has dates as texts. Like: '2021_01' (I will reefer this column as TextDate)

I convert it to '2021-01' with this code:
SELECT REPLACE(at.TextDate,'_','-') as DataFormat FROM tableName at

But when I try to cast it to date, or convert it to date, I always get 'Missing expression' errors. I tried this:

SELECT REPLACE(CONVERT(VARCHAR(7), at.TextDate, 126, '_', '-') as date FROM tableName at  

But it will give me errors. Any suggestion?

Answer 1

convert means something completely different in Oracle than it does elsewhere. You need the to_date() function:

SELECT TO_DATE(at.textDate, 'YYYY_MM') as DataFormat FROM tableName at

If you want to display it in a particular format then you can either let your client/application do that - most clients by default will use your session's NLS_DATE_FORMAT setting - or explicitly convert it back to a string with the complementary to_char() function.

db<>fiddle

The valid date elements are also in the documentation. You should only convert to a string for display though; while you are manipulating or storing it you should treat it as a date.

---

How can I filter last 3 months with it?

You need to use Oracle syntax, not SQL Server or other syntax. You also can't refer to a column alias in the same level of query. SO you can recalculate the date value; or as your string format is relatively sane you can convert the target date to a string and compare that, which might allow an index to be used. Something like:

SELECT TO_DATE(at.textDate, 'YYYY_MM') as DataFormat
FROM tableName at
WHERE at.textDate >= TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -3), 'YYYY_MM')

db<>fiddle