SQL - Get max date from dd/mm/yyyy formated column

Question

I have db table which have column name STATUSDATE. Type of this column is varchar2 and that column already have data in dd/mm/yyyy format. And i want get the recent date(max date). I used max() method for this but it not give the correct result, as example consider following dates

31/08/2014

01/09/2016

after using max(STATUSDATE) the result is 31/08/2014. I'm using oracle db.

I'm try to use following quarry but since above problem its give incorrect results

SELECT * FROM MY_DB.MY_TABLE t
inner join (
    select CLIENTNAME, max(STATUSDATE) as MaxDate
    from FROM MY_DB.MY_TABLE
    group by CLIENTNAME
) tm on t.CLIENTNAME = tm.CLIENTNAME and t.STATUSDATE = tm.MaxDate

please can anyone suggest proper way to do this Thank You

http://stackoverflow.com/questions/7118170/sql-server-select-only-the-rows-with-maxdate — Dheeraj Kumar, Aug 08 '16 at 11:24
i think you cant use max without changing the column type, but you can do what you want in a hard way. — Sandun Chathuranga, Aug 08 '16 at 11:26

Gordon Linoff · Accepted Answer · 2016-08-08T11:53:53.347

6

Moral: Don't store dates as strings. Databases have built-in types for a reason.

So, convert to a proper date and take the max, but you don't need a JOIN for this:

select t.*
from (select t.*,
             rank() over (partition by client_name
                          order by to_date(statusdate, 'DD/MM/YYYY') desc
                         ) as seqnum
      from my_db.my_table t
     ) t
where seqnum = 1;

edited Aug 08 '16 at 11:53

answered Aug 08 '16 at 11:22

Gordon Linoff

1,242,037
58
646
786

this quarry gives different result, but it still have the problem that I have mention in the question – Kani Aug 08 '16 at 11:45
it is given 2014 data instead of 2016 data – Kani Aug 08 '16 at 11:50
1

@Kani . . . I misread the question. For the maximum date, use `desc` on the `order by` rather than the default `asc`. – Gordon Linoff Aug 08 '16 at 11:54

score 0 · Answer 2 · edited Aug 08 '16 at 13:59

0

There is no need of inner join. You can do simply that:

select CLIENTNAME, desnse_rank() over (partition by client_name order by statusdate desc ) as MaxDate
FROM MY_DB.MY_TABLE
where maxdate =1
group by CLIENTNAME

edited Aug 08 '16 at 13:59

Alex M

2,756
7
29
35

answered Aug 08 '16 at 12:29

Faraz Naqvi

1

Your quarry gives this error >> ERROR: ORA-00904: "MAXDATE": invalid identifier – Kani Aug 09 '16 at 03:22

SQL - Get max date from dd/mm/yyyy formated column

2 Answers2