Re: Why does Oracle 9i show 10/30/05 with a d or s after the time and what does the XXFF time format mean?

From: HansF <News.Hans_at_telus.net>
Date: Thu, 10 Nov 2005 21:06:29 GMT
Message-Id: <pan.2005.11.10.21.06.28.86907@telus.net>

On Tue, 08 Nov 2005 08:00:02 -0600, colin_lyse wrote:

> NLS_TERRITORY AMERICA
>
> NLS_DATE_FORMAT MM/DD/YYYY
> NLS_DATE_LANGUAGE AMERICAN
> NLS_SORT BINARY
> NLS_TIME_FORMAT HH.MI.SSXFF AM
> NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
> NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
> NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
>
> the main issue is with the following
> ESP_NAME LOAD_DATE LOAD_VAL
> 1214 2004-10-31 01:00d 774.401
> 1214 2004-10-31 01:00s 754.4165
> 1214 2004-10-31 02:00s 754.4165
> 1214 2004-10-31 03:00s 744.3678
> 1214 2004-10-31 04:00s 738.4426
>

Pretty please - show us the SQL statement and the table describe. The NLS settings and the examples simply do not match up. You are giving us incomplete information.

You tell us the column is of type 'DATE' and yet a type 'DATE' should show up as 'MM/DD/YYYY'. A walk through date and timestamp follows ...

SQL> create table test ( test_date date );

Table created.

SQL> insert into test values (

          to_date('07-FEB-2004 11:25:36','DD-MON-YYYY HH:MI:SS'));

1 row created.

SQL> select * from test;

TEST_DATE

---

07-FEB-04 SQL> alter session set nls_date_format='MM/DD/YYYY';

Session altered.

SQL> select * from test;

TEST_DATE

---

02/07/2004

SQL> alter table test add test_timestamp timestamp;

Table altered.

SQL> update test set test_timestamp=test_date;

1 row updated.

SQL> select * from test;

TEST_DATE

---

TEST_TIMESTAMP

---

02/07/2004
07-FEB-04 11.25.36.000000 AM SQL> rem Radix is the 'decimal' separator in the NLS

SQL> alter session set NLS_TIMESTAMP_FORMAT='DD-MON-RR HH.MI.SSXFF AM';

Session altered.

SQL> alter session set NLS_TERRITORY='GERMANY';

Session altered.

SQL> select * from test;

TEST_DAT

---

TEST_TIMESTAMP

---

07.02.04
07.02.04 11:25:36,000000

SQL> rem and the FF indicates the decimal fraction

SQL> alter session set NLS_TIMESTAMP_FORMAT='DD-MON-RR HH.MI.SSXFF3';

Session altered.

SQL> select * from test;

TEST_DAT

---

TEST_TIMESTAMP

---

07.02.04
07-FEB-04 11.25.36,000 SQL>

-- 
Hans Forbrich                           
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard_at_gmail.com   
*** Top posting relies guarantees I won't respond. ***

Received on Thu Nov 10 2005 - 15:06:29 CST