Re: group by for column of type date

From: Michael Howitz <mhowitz_at_firemail.de>
Date: Wed, 27 Nov 2002 13:38:47 +0100
Message-ID: <3DE4BCD7.7090800@firemail.de>

Alex Filonov wrote:
[..]
>
> 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.

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)

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

[...]

-- 
mac

real email-adress: mac clondiag com

Received on Wed Nov 27 2002 - 06:38:47 CST