CODEWITHSUNDEEP
sqloracle-database
Ashok.N
Ashok.N

Reputation: 1391

SQL query to retrieve last twelve months data for Oracle database

I am using Oracle database. I have a table called "TEST" where the dates/timestamps(These are stored as "Char" in my case) are stored in the following format. Now I want to retrieve the records of last twelve months based on today's date. What would be the correct way to do that?

TESTCOLUMN
------------
2019-06-28-02.01.07.327240
2020-06-28-04.49.12.480240
2020-06-28-05.05.10.681240

Upvotes: 0

Views: 560

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

Although storing values in a string is not recommended, your format is comparable. So you can do the comparison using strings rather than date/timestamps. Assuming your values are only in the past:

where testcolumn >= to_char(SYSTIMESTAMP, -12), 'YYYY-MM-DD HH24.MI.SS.FF')

This has an advantage over Tejash's solution, because this can make use of an index (or partitions) on testcolumn. Moving the date manipulations only on the "constants" (i.e. the system timestamp) helps the Oracle optimizer.

Upvotes: 0

Popeye
Popeye

Reputation: 35900

I think you need to use the ADD_MONTHS function and BETWEEN clause as follows:

SELECT * FROM YOUR_TABLE
WHERE TO_TIMESTAMP(YOUR_COLUMN,'YYYY-MM-DD HH24.MI.SS.FF') 
      BETWEEN ADD_MONTHS(SYSTIMESTAMP,-12) AND SYSTIMESTAMP;

Upvotes: 2

Related Questions