I need to pull data from tables with millions of rows and limit it to just the past two years. I am working on developing a SQL statement to enter into Power BI to limit what it pulls. Obviously I am a novice but I cannot figure this out and all suggested approaches are not working for me. It is also unclear to me how to convert, limit, and pull all of the data from this table.
The LOADED_DATE column is char(8) so first I used TO_DATE to convert it, as you can see, but I am not sure what I am missing.
SELECT TO_DATE(LOADED_DATE, 'YYYYMMDD')
FROM ELLIPSE.MSF26A
WHERE LOADED_DATE >= ADD_MONTHS(SYSDATE,-24)
SQL Error [1861] [22008]: ORA-01861: literal does not match format string
If your loaded_date string column is indexed then you can convert the filter value to a string too:
SELECT TO_DATE(LOADED_DATE, 'YYYYMMDD')
FROM ELLIPSE.MSF26A
WHERE LOADED_DATE >= TO_CHAR(ADD_MONTHS(SYSDATE,-24), 'YYYYMMDD')
This will work because the format the date strings are stored as will sort alphabetically.
At the moment you are doing LOADED_DATE >= ADD_MONTHS(SYSDATE,-24) which is implicitly converting LOADED_DATE to a date type using your session's NLS settings, which don't match your string value format - hence the error - but isn't a good idea even if they do happen to match for you; they might not for someone else running the query. Explicitly converting to a date with the format specified avoids the problem, but would prevent a simple index on the column being used as every value in the table has to be converted before it can be compared. Even if there isn't an index, that's still a lot of unnecessary conversions that have to be performed.
It would still be better to store the values as dates, and index/compare them as dates, but if you are stuck with strings then use the most efficient comparison you can.