Re: group by for column of type date

Tim Cross <tcross_at_pobox.une.edu.au> wrote in message news:<87r8df3mxv.fsf_at_blind-bat.une.edu.au>...
> 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?
>

TRUNC is more efficient. It just truncates the date, when TO_CHAR also converts it into the string. You also completely insure yourself against date conversion problems. For example, if you order by date converted to 'YYYY-MM-DD', you are OK most of the time. If you use 'YY-MM-DD', results are completely wrong if you have dates it 2 different centuries. You wouldn't believe how many statements I modified for Y2K compliance because they used conversion with 'YY-MM-DD' mask.

> 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.
>

I usually trust people and their knowledge of SQL. On the other hand, if they don't know TRUNC function, they most probably don't know TO_CHAR function good enough. And they probably don't know how Oracle work with dates generally. In this case it's quite good if they are forced to read manual. Which they should've done in the first place.

> Tim
Received on Mon Nov 25 2002 - 10:11:17 CST