| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.tools -> Re: How do I change the format of a date?
Just a small thought. What if you actually saved the string as a date!! Like:
Mydate := to_date(your_date_string, 'MM/DD/YY');
What happens?
Best regards
Poul Kornmod
"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 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> to_char(to_number(to_char
(sysdate,
> > 'dd'))) || '/' ||</tt>
> > <br><tt> to_char(sysdate, 'yy')
</tt>
> > <br><tt>from 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. 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:16:23 CST
 |
 |