Re: Sort by foreign date column

On Tue, 21 Aug 2001, 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!

What version are you using? For me it orders by date.
sszabo=> create table datetest (d date);
CREATE
sszabo=> insert into datetest values ('12/11/2001');
INSERT 798850 1
sszabo=> insert into datetest values ('11/12/2001');
INSERT 798851 1
sszabo=> select * from datetest order by d;
d
------------
2001-11-12
2001-12-11
(2 rows)

sszabo=> select TO_CHAR(d, 'DD-MM-YYYY') from datetest order by d asc;
to_char
------------
12-11-2001
11-12-2001
(2 rows)

which appears to me to be date ordered not char ordered.