Home » SQL & PL/SQL » SQL & PL/SQL » Date format (oracle)
Date format [message #328431] Fri, 20 June 2008 03:10 Go to next message
convey05
Messages: 43
Registered: December 2007
Location: CHENNAI
Member
Hi,
Can anyone tell me how to resolve the problem

I have a date column in which date is stored as 'mm/dd/yyyy' format.
When i tried to query the date column in the procedure it is displaying the date with the last digits as year.I have used to_date('col','dd-mon-yyyy') format also
eg -- 15-may-08 instead of 15-may-2008


Thanks in advance
Re: Date format [message #328434 is a reply to message #328431] Fri, 20 June 2008 03:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I have a date column in which date is stored as 'mm/dd/yyyy' format.

In this case this is NOT a date column, this a string (VARCHAR2) one that represents a date.

Quote:
I have used to_date('col','dd-mon-yyyy') format

You can't do this on a string, you can do this on a date.

Regards
Michel
Re: Date format [message #328439 is a reply to message #328431] Fri, 20 June 2008 03:28 Go to previous messageGo to next message
soni_7
Messages: 33
Registered: July 2005
Member
Hi,

Try
to_char(to_date('col','dd-mon-yyyy'),'dd-mon-yyyy')


Regards
Soni
Re: Date format [message #328440 is a reply to message #328434] Fri, 20 June 2008 03:29 Go to previous messageGo to next message
convey05
Messages: 43
Registered: December 2007
Location: CHENNAI
Member
Hi,
In the table for that column the data type is DATE.
Then what i have to do to resolve the problem.

Thanks in advance
Re: Date format [message #328443 is a reply to message #328439] Fri, 20 June 2008 03:36 Go to previous messageGo to next message
convey05
Messages: 43
Registered: December 2007
Location: CHENNAI
Member
Hi,
Even then i same issue i am getting.
Re: Date format [message #328445 is a reply to message #328443] Fri, 20 June 2008 03:47 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If the column in the table is a DATE, then you just need to wrap it in a TO_CHAR when you select it to specify any date format that yow wish.

If you want to see the date like 1st April 2008, you'd use TO_CHAR(date_col,'fmddth Month yyyy')

If you want to see it as 01-APR-2008, you'd use TO_CHAR(date_col,'dd-MON-yyyy');

Here's the documentation about date format models.
Re: Date format [message #328526 is a reply to message #328445] Fri, 20 June 2008 06:57 Go to previous messageGo to next message
convey05
Messages: 43
Registered: December 2007
Location: CHENNAI
Member
Hi,
I have created a procedure like the one displayed below.
when I executed the procedure i got the output as
13-may-08
kindly let me know what to be done to get the whole year

create or replace procedure test_Date( p_date date)
is
a date;
begin
a := to_char(p_date,'dd-mon-rrrr');
dbms_output.put_line(a);
end;


exec test_Date('13-may-2008');

Thanks in advance
Re: Date format [message #328531 is a reply to message #328526] Fri, 20 June 2008 07:12 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Hmm - where to start. You've got this whole date/string thing quite badly wrapped round your neck.

1) You call the procedure like this: test_Date('13-may-2008');
'13-may-2008' is a string. Your procedure takes a date as an input parameter.

2) In the procedure, you declare variable A as type DATE
You then assign to it the value to_char(p_date,'dd-mon-rrrr');
That's a string you're assigning to it.

3) Dbms_Output.Put_line takes a string as a parameter.
You pass it the variable A, which is type DATE

At each of these steps, oracle has to do an implicit type conversion. I don't know your setup, but I'll guess that on the last conversion (the DBMS_OUTPUT.PUT_LINE) it's using the default date mask of 'DD-MON-RR'.

Try using this code instead:


create or replace procedure test_Date( p_date date) is
begin
  dbms_output.put_line(to_char(p_date,'dd-mon-rrrr'));
end;  
/

begin
  test_Date(to_date('13-may-2008','dd-mon-yyyy'));
end;
/
Re: Date format [message #328534 is a reply to message #328431] Fri, 20 June 2008 07:15 Go to previous messageGo to next message
msmallya
Messages: 66
Registered: March 2008
Location: AHMEDABAD, GUJARAT
Member
Hi,

Set "nls_date_format" parameter to display format
dd-mon-yyyy in your session.

Regards

MSMallya

[Updated on: Fri, 20 June 2008 07:22]

Report message to a moderator

Re: Date format [message #328544 is a reply to message #328534] Fri, 20 June 2008 07:53 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
msmallya wrote on Fri, 20 June 2008 14:15
Hi,

Set "nls_date_format" parameter to display format
dd-mon-yyyy in your session.

Regards

MSMallya


Wrong!
Do not rely on implicit datatype conversions! Use explicit conversions, with format-masks.
Re: Date format [message #328546 is a reply to message #328544] Fri, 20 June 2008 07:57 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I agree. Implicit type conversions just mean that your code stops workng when someone changes the NLS settings.
Previous Topic: ORA-06550: line 1, column 20: PLS-00103: Encountered the symbol "" when expecting
Next Topic: Regarding formatting using pivot Queries
Goto Forum:
  


Current Time: Mon Dec 05 20:58:13 CST 2016

Total time taken to generate the page: 0.12161 seconds