Find min date from a column which has values DD-MMM-YY format stored as string

Question

One of the column(file_date) in a table is of type string and has values of dates in DD-MMM-YY format. How do I find min(date) from the column.

I wrote simple query Select min(file_date) from tablename

It gives output as: 01-Dec-22

But there are dates from earlier months present in the table example 28-Aug-22,31-Oct-22,14-Nov-22

The expected output is 28-Aug-22 as this is the earliest date from which data is present in the table.

Answer 1

Oracle:

WITH     -- sample data
    tbl (ID, FILE_DATE) AS
        (
            Select 1, '01-Dec-22' From Dual Union All
            Select 1, '21-Jan-22' From Dual Union All
            Select 1, '01-Dec-21' From Dual Union All
            Select 1, '01-Jan-23' From Dual Union All
            Select 1, '10-Dec-20' From Dual Union All
            Select 1, '11-Jan-23' From Dual 
        )
-- your SQL
Select Min(To_Date(FILE_DATE, 'dd-Mon-yy')) "MIN_DATE" From tbl;

MIN_DATE
---------
10-DEC-20

More about here