Re: group by for column of type date

afilonov_at_yahoo.com (Alex Filonov) writes:

> Matthias Rogel <rogel_at_web.de> wrote in message news:<arfjs7$hcavn$1_at_ID-86071.news.dfncis.de>...
> > SELECT TO_CHAR(crea_date, 'YYYY-MM-DD') crea_date,
> > COUNT(*) crea_date_cnt
> > FROM cct_access
> > WHERE crea_date >= TO_DATE('2002-11-06 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
> > GROUP TO_CHAR(crea_date, 'YYYY-MM-DD');
> >
>
> Better:
>
> GROUP BY TRUNC(crea_date)
>

Just wondering....

Why is TRUNC(crea_date) better than using TO_CHAR(crea_date, 'YYYY-MM-DD')? Is it because TRUNC is more efficient or does it have something to do with possible inconsistencies in the to_char method which may result from different locale sorting orders or character sets?

While I don't have any problem with TRUNC, I personally like the to_char approach because I think its clearer to others exactly what your ordering by - when I first saw the use of TRUNC in this way, it was not obvious what was happening and I had to check the manual on the TRUNC function to work it out.

Tim Received on Wed Nov 20 2002 - 16:18:04 CST