CODEWITHSUNDEEP
sqloracle-databasedatetime
Salvador
Salvador

Reputation: 1

Select latest 13 months data

I would like to select the latest 13 months data from an oracle database. The DATE is showing as '16/04/2020, 00:00:00'. I have tried the following however it is ignoring the where statement and returning all data:

select *
from DM.DATE COMMON_DATE
where
to_date(COMMON_DATE.DATE,'dd/mm/yyyy') <= add_months(trunc(sysdate, 'MON'), -1)

thanks

Upvotes: 0

Views: 610

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Your date datatype looks like a string, because of the comma. I suggest that you put your efforts into fixing the data! Dates should be stored using date, timestamp or related types.

In your case, though, you can parse out the date. I assume that you want:

where to_date(substr(common_date.date, 1, 10), 'DD/MM/YYYY') >= add_months(trunc(sysdate, 'MON'), -13)

EDIT:

If the column is already a date, then you don't need to convert it:

where common_date.date >= add_months(trunc(sysdate, 'MON'), -13)

Upvotes: 2

Related Questions