0

I am using Oracle database. I have a table called "TESTbst365体育投注网址大全" 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
2
0

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;
| improve this answer | |
  • Thanks. It worked like a charm. +1 for your quick response. – Ashok.N Jul 1 at 5:42
0
0

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 testcolumnbst365体育投注网址大全. Moving the date manipulations only on the "constants" (i.e. the system timestamp) helps the Oracle optimizer.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.