Re: group by for column of type date

"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

******************************************