| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: dates
Thomas Kyte wrote:
>
> A copy of this was sent to "Eamonn Keating" <oristech_at_tinet.ie>
> (if that email address didn't require changing)
> On Mon, 9 Aug 1999 14:59:03 +0100, you wrote:
>
> >When I insert a date into an Oracle table using to_date( sysdate,
> >'dd-mon-yyyy') in inserts it in dd/mm/yy format. When I select it using
> >to_char(REV_DATE,'dd/Mon/yyyy') 1999 appears as 0099. What settings must I
> >change in order to use yyyy format
> >Many thanks
> >Eamonn
> >
> >
>
> when you insert
>
> to_date( sysdate, 'dd-mon-yyyy' )
>
> that is really doing:
>
> to_date( TO_CHAR(sysdate), 'dd-mon-yyyy' )
>
> since to_date expects a character string, not a DATE as an argument. This
> implicit to_char() is using the default date mask (probably dd-mon-yy) to
> translate the string so this is just like:
>
> to_date( '09-AUG-99', 'dd-mon-yyyy' )
>
> which is why you are getting 0099, not 1999. You are inserting 0099!
>
> you do not want to TO_DATE a field that is already a date -- you should just
> insert SYSDATE, not to_date(sysdate) (sysdate is a date, no conversion needed).
> You use to_date to convert character strings into dates.
>
> --
> See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
> Current article is "Part I of V, Autonomous Transactions" updated June 21'st
>
> Thomas Kyte tkyte_at_us.oracle.com
> Oracle Service Industries Reston, VA USA
>
> Opinions are mine and do not necessarily reflect those of Oracle Corporation
Eamonn,
(Adding to Thomas's post) The RRRR format mask can then be used to avoid the 0099 style of error...Sending '99' to the 'RRRR' format mask will give you 1999 not 0099.
HTH
--
"Some days you're the pigeon, and some days you're the statue." Received on Mon Aug 09 1999 - 09:05:12 CDT
 |
 |