Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: group by for column of type date

Re: group by for column of type date

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Wed, 27 Nov 2002 13:39:26 -0000
Message-ID: <3de4cb0e$0$8514$ed9e5944@reading.news.pipex.net>


"Michael Howitz" <mhowitz_at_firemail.de> wrote in message news:3DE4BCD7.7090800_at_firemail.de...
> a short test shows:
>
> create table date_test (t date);
> insert into date_test select sysdate from dual;
>
> now using a german database (NLS_LANG=german_germany.WE8DEC)
>
> SQL> select trunc(t) from date_test;
>
> TRUNC(T)
> --------
> 27.11.02
>
>
> now using an american database (NLS_LANG=AMERICAN_AMERICA.WE8DEC)
>
> SQL> select trunc(t) from date_test;
>
> TRUNC(T)
> ---------
> 27-NOV-02
>
>
> that's not really equal and I don't kow the rules, so I use TO_CHAR to
> set the format & handle the locale problems by myself (i.e. setting them
> equal for database and host-language PHP4)

The original question was about grouping by date. Group by trunc(date_column) will group all rows from the same day together, it is more efficient (for the reasons Alex gives though I do wonder how big the performance impact actually is), Your test seems just to show that the *presentation* of the date is different dependent upon NLS_LANG.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************
Received on Wed Nov 27 2002 - 07:39:26 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US