0

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.

Xenon
  • 11
  • 2

1 Answers1

0

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

d r
  • 3,848
  • 2
  • 4
  • 15
  • This gives error as " unmatched function: to_date(string,string)" – Xenon Jan 12 '23 at 22:30
  • @Xenon What is your RDBMS? This is Oracle sintax and works ok. Anyway, look up into your database documentation for an appropriate sintax. There sure is a set of data conversion sintax. What you need is to convert string to a date and select the Min(DATE)... – d r Jan 13 '23 at 05:27