Oracle-L: Re: to_date function and NLS settings on client

From: Arup Nanda <arupnanda_at_hotmail.com>
Date: Wed, 20 Nov 2002 11:34:04 -0800
Message-ID: <F001.005082DC.20021120113404@fatcity.com>

to_date function and NLS settings on clientHelmut,

The cleint nls_date setting can be done by

ALTER SESSION SET NLS_DATE_FORMAT = 'dd.mm.yyyy';

The currect setting can be seen from v$nls_parameters and if the session has explicitly set it, the new value will be shown.

However, I feel this problem more than that. Please note that the DATE datatype is not just date, but timestamp as well. Your query is comparing a date field to '01.10.1950', which means the 00:00:00 of 1st Oct 1950. Your data field could containt 13:30:31 of 1st Oct 1950. Therefore the comparison fails. Have you tried this:

select ... from ... where trunc(datefield1) = '01.10.1950'

select .. from .. where datefield1 between '01.10.1950' and '02.10.1950'

Hop this helps.

Arup Nanda
www.proligence.com

Original Message ----- From: Daiminger, Helmut To: Multiple recipients of list ORACLE-L Sent: Wednesday, November 20, 2002 8:03 AM Subject: to_date function and NLS settings on client

Hello!

We have several Windows clients connecting to our db-server. Every client issues the statement:

select .... from thabe where date = to_date('01.10.1950','DD-MM-YYYY');

Some clients return the correct number of rows where as others return nothing.

I know that this has something to to with the NLS settings on the client.

How do I find out the NLS settings on the client? Just in the registry?

How coem this does not work although I explicity specify a format mask?

This is 8.1.7 on Solaris.

Thanks,
Helmut

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Arup Nanda
INET: arupnanda_at_hotmail.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Wed Nov 20 2002 - 13:34:04 CST