Home » SQL & PL/SQL » SQL & PL/SQL » Date format (Oracle 9i)
Date format [message #427157] Wed, 21 October 2009 06:29 Go to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi,
I am getting error while trying to display the date column data in 'mm/dd/yyyy' format.


Please find the scripts:-
CREATE TABLE PARK1
(
  VENDOR_CODE       VARCHAR2(30 BYTE),
  TRANSACTION_TYPE  VARCHAR2(25 BYTE),
  TRANSACTION_DATE  DATE
)

INSERT INTO PARK1 ( VENDOR_CODE, TRANSACTION_TYPE, TRANSACTION_DATE ) VALUES ( 
'20', 'CREDIT',  TO_Date( '06/02/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO PARK1 ( VENDOR_CODE, TRANSACTION_TYPE, TRANSACTION_DATE ) VALUES ( 
'20', 'STANDARD',  null); 
INSERT INTO PARK1 ( VENDOR_CODE, TRANSACTION_TYPE, TRANSACTION_DATE ) VALUES ( 
'20', 'CREDIT',  TO_Date( '05/25/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));



select vendor_code, transaction_type, to_date(to_char(transaction_date,'dd-mon-yyyy'),'mm/dd/yyyy') from park1


I am getting error :-

ORA-01858: a non-numeric character was found where a numeric was expected

The data I received from client in parking table is as in given inserts. Please help me on this....seems I am missing on a small thing....please advice as I am mainly getting error due to the NULL value in this column in second insert script. Please advice as how to display the data for all table in 'mm/dd/yyyy' format when there are null values in the date column for some records.

Regards,

Mahi

[Updated on: Wed, 21 October 2009 06:39]

Report message to a moderator

Re: Date format [message #427159 is a reply to message #427157] Wed, 21 October 2009 06:38 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This is your problem:
to_date(to_char(transaction_date,'dd-mon-yyyy'),'mm/dd/yyyy')

You are converting a date into a DD-MON-YYYY string, and then attempting to convert it basck into being a date using a format mask of mm/dd/yyyy

If you're just trying to display the date as mm/dd/yyyy. just do:
select vendor_code
      ,transaction_type
      ,to_char(transaction_date,'mm/dd/yyyy')
from   park1
Re: Date format [message #427160 is a reply to message #427157] Wed, 21 October 2009 06:43 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi Jrow,
Actually I have to insert values from this table into another table:-

create table park2 as select * from park1 where 1=2

INSERT INTO park2
          SELECT vendor_code, transaction_type, 
                 to_date(to_char(transaction_date,'mm/dd/yyyy'),'mm/dd/yyyy')
            FROM park1
           WHERE vendor_code IS NOT NULL;


This is working.... Thanks!!

[Updated on: Wed, 21 October 2009 06:46]

Report message to a moderator

Re: Date format [message #427161 is a reply to message #427160] Wed, 21 October 2009 06:46 Go to previous messageGo to next message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
If what's in the other table is also a date field you don't have to worry about any formats or anything. Just use the date field directly.

Applying to_char and then to_date again is just meaningless..

[Updated on: Wed, 21 October 2009 06:48]

Report message to a moderator

Re: Date format [message #427164 is a reply to message #427161] Wed, 21 October 2009 06:52 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
Applying to_char and then to_date again is just meaningless..
That is putting it politely.
Re: Date format [message #427167 is a reply to message #427160] Wed, 21 October 2009 07:00 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
As c_stenersen said, you don't need to nest the to_date & to_char fields.

Just do:
INSERT INTO park2
          SELECT vendor_code
                ,transaction_type
                ,transaction_date
          FROM   park1
          WHERE  vendor_code IS NOT NULL;
Re: Date format [message #427173 is a reply to message #427167] Wed, 21 October 2009 07:13 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Thats really great!! I didn't noticed that the date format client wanted, he has sent in that format only....so no need to change Smile
Re: Date format [message #427178 is a reply to message #427157] Wed, 21 October 2009 07:31 Go to previous message
cookiemonster
Messages: 12412
Registered: September 2008
Location: Rainy Manchester
Senior Member
You do realise that dates aren't stored in any format (that you'd recognize) in database don't you?
You only need to_char them when you're selecting them and to_Date them when you're inserting them.
Previous Topic: How to insert a txt file into a Blob Column?
Next Topic: Parallel execution of Queries in a Stored Procedure
Goto Forum:
  


Current Time: Wed Dec 07 08:41:24 CST 2016

Total time taken to generate the page: 0.15004 seconds