Re: group by for column of type date

Michael Howitz <mhowitz_at_firemail.de> wrote in message news:<3DE342A1.3080104_at_firemail.de>...
> Alex Filonov wrote:
> [..]
> > 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.
>
> but TRUNC has the problem that it depends on the locale-settings. my

TRUNC (date function) does not depend on locale settings. It just truncates the
date.
Here's function definition from 8.1.7 and 9.2.0.1 SQL language reference:

TRUNC returns d with the time portion of the day truncated to the unit specified by the format model fmt. If you omit fmt, d is truncated to the nearest day.

I don't see anything about locale settings here. It might be locale specific
when you truncate to week, month, year etc., but I don't see how it can be
locale specific when truncates to the day.

TO_CHAR converts date into string. Which can be locale specific, even with
specified format.

> software should work with different locale settings & i have also to use
> date-conversion in the host-language (PHP4). so if the locale changes i
> don't know how to reflect this change in php-code.
> the only way would be to do all date-conversion using oracle, but this
> is not really good because it is too oracle specific & my application
> should also be able to use another database instead.
Received on Tue Nov 26 2002 - 10:41:45 CST