Re: dates

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 Received on Mon Aug 09 1999 - 10:24:12 CDT