date format issue

Question

Hello,

I am using oracle database. I have one table with column END_DATE as varchar datatype. The data in the column is like 02/01/2025 in DD/MM/YYYY date format. I am using between date query, but it says - Not a valid month

Please suggest any valid date format  so i can convert those dates in valid format. I have tried to_char(), to_date and trunc()

My query :

select * from table where END_DATE between to_date('01/12/2024', 'DD/MM/YYYY.') and to_date('10/02/2025', 'DD/MM/YYYY.')

OR

How can I write query to get last  3 month data from table according to current date. For Ex: BETWEEN SYSDATE-90 and SYSDATE

 

Thanks in adv !

Answer 1

Hi Sushant,

SELECT * FROM table
WHERE TO_DATE(END_DATE, 'DD/MM/YYYY') BETWEEN TO_DATE('01/12/2024', 'DD/MM/YYYY')  AND TO_DATE('10/02/2025', 'DD/MM/YYYY');

TO_DATE(END_DATE, 'DD/MM/YYYY') to convert VARCHAR to DATE

Removed the extra dot (.) from 'DD/MM/YYYY.'

To Get Last 3 Month of Data

SELECT * FROM table
WHERE TO_DATE(END_DATE, 'DD/MM/YYYY')  BETWEEN TRUNC(SYSDATE) - 90 AND TRUNC(SYSDATE);

TRUNC(SYSDATE) - Used to remove the time part

TRUNC(SYSDATE) - 90 - To get the date before 90 days(3month)

If you save a date in this 02/01/2025 format, it's better to change the END_DATE column datatype to a date permanently.

You can alter the table and update the existing values.

ALTER TABLE table MODIFY END_DATE DATE;

UPDATE table SET END_DATE = TO_DATE(END_DATE, 'DD/MM/YYYY');