Re: Sort by foreign date column

"Andrey Y. Mosienko" wrote:
>
> Stephan Szabo wrote:
> >
> > On Tue, 21 Aug 2001, Andrey Y. Mosienko wrote:
> >
> > >
> > > I have table with date type column:
> > >
> > > chdate date;
> > >
> > > SELECT chdate from mytable;
> > >
> > > chdate
> > > 1999-01-02
> > >
> > > But in Russia we have the next date format: DD-MM-YYYY.
> > >
> > > When I do coversion to char in SELECT:
> > > TO_CHAR(chdate,'DD-MM-YYYY') everything is ok, but sort by this column
> > > executes as sort for char type.
> > >
> > > How can I display my native date format and do right sorting by this column?
> >
> > Wouldn't
> > select to_char(chdate, 'DD-MM-YYYY') from mytable order by chdate;
> > work?
>
> Works. But sorting performs as for CHAR TYPE!

I don't know why the sorting isn't functioning correctly for the
'date' data type (locale issue?), but this should definitely work:

SELECT TO_CHAR(chdate, 'DD-MM-YYYY')
FROM mytable
ORDER BY EXTRACT(EPOCH from chdate);

Hope that helps,

Mike Mascari
mascarm(at)mascari(dot)com