Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: How do I change the format of a date?

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

From: Andrew Hardy <Andrew.Hardy_at_AdvanticaTech.com>
Date: Thu, 14 Dec 2000 08:45:03 -0000
Message-ID: <91a1a4$jt2$1@sun-cc204.lut.ac.uk>

It's because you are trying to put a wrongly formatted date into your date variable i.e. you are trying to put in dd/mm/yy when your date format is by default dd-mon-yy. So you either need to create strings that look like real dates in the default date format, or (and preferred because you *can* change the default date format for your database with nls_date_format) explicitly tell Oracle what date format you are using.

You need to change your MyDate assignment to something like:

  5 MyDate := to_date (to_char(to_number(to_char(MyDate, 'mm'))) || '/' ||

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

Andy
tirthankara <tirthankara_at_my-deja.com> wrote in message news:919i16$ujc$1_at_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?
>
> T
>
> 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
> > <p><tt>select TO_CHAR(sysdate, 'mm/dd/yyyy')</tt>
> > <br><tt>from&nbsp;&nbsp; dual;</tt>
> > <p>or if you really do not want the leading zeros
> > <p><tt>select to_char(to_number(to_char(sysdate, 'mm'))) || '/'
> ||</tt>
> > <br><tt>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; to_char(to_number(to_char
 (sysdate,
> > 'dd'))) || '/' ||</tt>
> > <br><tt>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; to_char(sysdate, 'yy')
 </tt>
> > <br><tt>from&nbsp;&nbsp; dual;</tt><tt></tt>
> > <p><tt>Jay!!!</tt>
> > <p>tirthankara wrote:
> > <blockquote TYPE=CITE>Hello,
> > <p>How can I change the format of a DATE from 03-JUN-99 to 6/3/1999?
> > <p>Please help.&nbsp; Thanks.
> > <p>Sent via Deja.com
> > <br><a

 href="http://www.deja.com/">http://www.deja.com/</a></blockquote>
> > </html>
> >
> > --------------00232C432D8F14035372E5C6--
> >
> >
>
>
> Sent via Deja.com
> http://www.deja.com/
Received on Thu Dec 14 2000 - 02:45:03 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US