Date Format [message #601248] |
Mon, 18 November 2013 22:36 |
|
mist598
Messages: 1195 Registered: February 2013 Location: Hyderabad
|
Senior Member |
|
|
Hi all,
I want to display the year 2013, but it displays 13 like below
declare
v_date date;
begin
v_date:=to_date('November 19 2013','Month DD YYYY');
dbms_output.put_line('Today Date Is:'||v_date);
end;
out put: Today Date Is:19-NOV-13
Can any one suggest, i am using Toad Database
Thank you
|
|
|
|
|
|
Re: Date Format [message #601255 is a reply to message #601253] |
Mon, 18 November 2013 23:16 |
|
mist598
Messages: 1195 Registered: February 2013 Location: Hyderabad
|
Senior Member |
|
|
Hi BlackSwan,
It was working fine On SQL Query, but not in Pl/SQL and below code was working fine
declare
v_date varchar2(14); -->Here I changed Datatype as Varchar2
begin
v_date:= to_char(TO_DATE('2012-JAN-12','YYYY-MON-DD'),'DD-MON-YYYY');
dbms_output.put_line('Today Date Is:'||v_date);
end;
Thank you
[Updated on: Mon, 18 November 2013 23:17] Report message to a moderator
|
|
|
|
|
Re: Date Format [message #601266 is a reply to message #601261] |
Tue, 19 November 2013 00:31 |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
The important thing to remember is that if you want to output a date in a specific format, use to_char (or change the default display format for dates) which is what BlackSwan was demonstrating.
|
|
|
Re: Date Format [message #601304 is a reply to message #601261] |
Tue, 19 November 2013 07:19 |
|
EdStevens
Messages: 1376 Registered: September 2013
|
Senior Member |
|
|
mist598 wrote on Mon, 18 November 2013 23:29Hi BlackSwan,
In my 1st post datatype is v_date date , lastly i changed as v_date varchar2(14), now i was working fine now
Thanks again sir, If i made a mistake when i posted sorry for that.
changing v_date to varchar is a huge red flag. Once you do that, you can never treat it like a date again. You need to fully understand the difference between storing (either in a table or as a pl/sql variable) data as a DATE data type and displaying it as a character string. And since a DATE (data type) is a binary structure, when displayed it is always converted to a character string.
see: http://edstevensdba.wordpress.com/2011/04/07/nls_date_format/ - But I want to store my date as ...
|
|
|