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 !
Sreenath KappoorPosted Feb 12, 2025, 4:06 AM
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 convertVARCHARtoDATERemoved the extra dot (
.) from'DD/MM/YYYY.'To Get Last 3 Month of DataSELECT * 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');