Re: Sort by foreign date column

and how about this:

SELECT TO_CHAR(chdate, 'DD-MM-YYYY')
FROM mytable
ORDER BY chdate::date;

>From: Mike Mascari <mascarm(at)mascari(dot)com>
>To: "Andrey Y. Mosienko" <feo(at)ttn(dot)ru>
>CC: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>, Postgres
><pgsql-general(at)postgresql(dot)org>
>Subject: Re: [GENERAL] Sort by foreign date column
>Date: Tue, 21 Aug 2001 03:16:55 -0400
>
>"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
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/users-lounge/docs/faq.html

_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp