I have a string date column in the format DD-MMM-YY (EX:29-Jul-18) which is same the filter value. I need to get records which are greater than the given date . But the filter is not working. It's retrieving all the records in the table. The table contains nearly 10M record. Can someone help ?

SELECT * FROM mytable WHERE `date_column` > '20-SEP-22'

CodePudding user response：

Just try this

SELECT * FROM mytable WHERE `date_column` > STR_TO_DATE('20-SEP-22','%d-%M-%y')

For more info, please refer here

I don't know which programming language you are using, but for maximum query performance, it's better to parse to respective date format before it reaching it to the query that you are supposed to execute. As you said this have million records, so inspect the execution time and choose better solution wisely.

CodePudding user response：

If you are working with SQL this might help https://www.sqltutorial.org/sql-date-functions/sql-convert-string-to-date-functions/