Home » SQL & PL/SQL » SQL & PL/SQL » NCLOB to Date - Urgent
NCLOB to Date - Urgent [message #209563] Fri, 15 December 2006 10:05 Go to next message
nachiketa.iyengar
Messages: 5
Registered: November 2006
Location: INDIA
Junior Member
Hi,

I have a column of datatype NCLOB and it returns a date!!!!(It not me who did the design).

I need this date to be returned in the format of 'dd/mm/yyyy', but it is returning me in the format of 'mm/dd/yyyy'.

Can anyone please tell me if there is any direct conversion to date format that I want from NCLOB?

I did try to_date() function, but unsatisfactory results Sad
Please tell me the solution.Its very urgent.

Nachiketa Iyengar




Re: NCLOB to Date - Urgent [message #209601 is a reply to message #209563] Fri, 15 December 2006 15:44 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Where's your test case???
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.6.0 - Production

SQL> select value from nls_database_parameters
  2  where parameter = 'NLS_NCHAR_CHARACTERSET';

VALUE
--------------------------------------------------------------------------------
AL16UTF16

SQL> CREATE TABLE NCLOB_TST(COL1  NCLOB);

Table created.

SQL> insert into NCLOB_TST values ('12/31/2000');

1 row created.

SQL> column col1 format a12
SQL> column col1_ddmmyyyy format a12
SQL> select col1, to_char(to_date(col1, 'mm/dd/yyyy'), 'dd/mm/yyyy') col1_ddmmyyyy from NCLOB_TST;

COL1         COL1_DDMMYYY
------------ ------------
12/31/2000   31/12/2000

SQL> 
Previous Topic: LTRIM mystery
Next Topic: Implementing 1 to many mandatory relatioship
Goto Forum:
  


Current Time: Thu Dec 05 19:35:02 CST 2024