Re: How do I change the format of a date?

From: tirthankara <tirthankara_at_my-deja.com>
Date: Thu, 14 Dec 2000 04:24:11 GMT
Message-ID: <919i16$ujc$1@nnrp1.deja.com>

Thank you both for responding. But my problem remains (and you thought this would be easy!) Your code (and mine too) works with sysdate, but not with variables. Take a look:

SQL> DECLARE
  2 MyDate DATE:= '01-OCT-00';
  3 BEGIN
  4 dbms_output.put_line(MyDate);
  5 MyDate := to_char(to_number(to_char(MyDate, 'mm'))) || '/' ||

  6        to_char(to_number(to_char(MyDate, 'dd'))) || '/' ||
  7        to_char(MyDate, 'yy');

8 dbms_output.put_line(MyDate);
9 END;
10 /
01-OCT-00
DECLARE
*
ERROR at line 1:
ORA-01843: not a valid month
ORA-06512: at line 5

It has to be a valid date since the datatype accepts it for the first dbms_output command. But it falls appart after that.

What do you think?

In article <3A381548.7C84EF13_at_pixar.com>, Jay Weiland <jay_at_pixar.com> wrote:
>
> --------------00232C432D8F14035372E5C6
> Content-Type: text/plain; charset=us-ascii
> Content-Transfer-Encoding: 7bit
>
> If you are asking about date format in SQL*Plus
>
> select TO_CHAR(sysdate, 'mm/dd/yyyy')
> from dual;
>
> or if you really do not want the leading zeros
>
> select to_char(to_number(to_char(sysdate, 'mm'))) || '/' ||
> to_char(to_number(to_char(sysdate, 'dd'))) || '/' ||
> to_char(sysdate, 'yy')
> from dual;
>
> Jay!!!
>
> tirthankara wrote:
>
> > Hello,
> >
> > How can I change the format of a DATE from 03-JUN-99 to 6/3/1999?
> >
> > Please help. Thanks.
> >
> > Sent via Deja.com
> > http://www.deja.com/
>
> --------------00232C432D8F14035372E5C6
> Content-Type: text/html; charset=us-ascii
> Content-Transfer-Encoding: 7bit
>
> <!doctype html public "-//w3c//dtd html 4.0 transitional//en">
> <html>
> If you are asking about date format in SQL*Plus
> <tt>select TO_CHAR(sysdate, 'mm/dd/yyyy')</tt>
> <tt>from   dual;</tt>
> or if you really do not want the leading zeros
> <tt>select to_char(to_number(to_char(sysdate, 'mm'))) || '/'
||</tt>
> <tt>       to_char(to_number(to_char
(sysdate,
> 'dd'))) || '/' ||</tt>
> <tt>       to_char(sysdate, 'yy')
</tt>
> <tt>from   dual;</tt><tt></tt>
> <tt>Jay!!!</tt>
> tirthankara wrote:
> <blockquote TYPE=CITE>Hello,
> How can I change the format of a DATE from 03-JUN-99 to 6/3/1999?
> Please help.  Thanks.
> Sent via Deja.com
> <a

href="http://www.deja.com/">http://www.deja.com/</a></blockquote>
> </html>
>
> --------------00232C432D8F14035372E5C6--
>
>

Sent via Deja.com
http://www.deja.com/ Received on Wed Dec 13 2000 - 22:24:11 CST