Home » SQL & PL/SQL » SQL & PL/SQL » Date Format
Date Format [message #601248] Mon, 18 November 2013 22:36 Go to next message
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 #601249 is a reply to message #601248] Mon, 18 November 2013 22:39 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member

  1* select to_char(sysdate,'YYYY-MM-DD') TODAY  FROM DUAL
SQL> /

TODAY
----------
2013-11-18

Re: Date Format [message #601252 is a reply to message #601249] Mon, 18 November 2013 22:53 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Hi BlackSwan,


Thank you early reply, if i want to display " 12-JAN-2012" ,what i can i do? any suggestion please

Thank you
Re: Date Format [message #601253 is a reply to message #601252] Mon, 18 November 2013 23:00 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
mist598 wrote on Mon, 18 November 2013 20:53
Hi BlackSwan,


Thank you early reply, if i want to display " 12-JAN-2012" ,what i can i do? any suggestion please

Thank you


  1* select  '2012-JAN-12',  to_char(TO_DATE('2012-JAN-12','YYYY-MON-DD'),'DD-MON-YYYY') from dual
SQL> /

'2012-JAN-1 TO_CHAR(TO_
----------- -----------
2012-JAN-12 12-JAN-2012

Re: Date Format [message #601255 is a reply to message #601253] Mon, 18 November 2013 23:16 Go to previous messageGo to next message
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 #601258 is a reply to message #601255] Mon, 18 November 2013 23:23 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member

  1  declare
  2      v_date varchar2(14);  -->Here I changed Datatype as Varchar2
  3  begin
  4  v_date:= to_char(TO_DATE('2012-JAN-12','YYYY-MON-DD'),'DD-MON-YYYY');
  5     dbms_output.put_line('Today Date Is:'||v_date);
  6* end;
SQL> /

PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL> /
Today Date Is:12-JAN-2012

PL/SQL procedure successfully completed.

SQL> 

Re: Date Format [message #601261 is a reply to message #601258] Mon, 18 November 2013 23:29 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
Hi 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.
Re: Date Format [message #601266 is a reply to message #601261] Tue, 19 November 2013 00:31 Go to previous messageGo to next message
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 Go to previous message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
mist598 wrote on Mon, 18 November 2013 23:29
Hi 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 ...
Previous Topic: Can we use constraints and Index in external table in oracle 10g
Next Topic: Function Name ?
Goto Forum:
  


Current Time: Thu Mar 28 15:25:36 CDT 2024