| 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: NEED HELP WITH "DATE" FORMAT
A copy of this was sent to Azam Mirza <aamirza_at_uno.edu>
(if that email address didn't require changing)
On Tue, 27 Jul 1999 22:57:46 -0500, you wrote:
>Hi
>I am loading some data into tables and one of the fields that is coming
>in is a date field. It is in a two digit format. Now on querying this
>data it shows me all those dates before 1949 as 2049 or so. I know this
>is because of 50 year pivot of date format that oracle uses and this is
>the default. Is there any way to change this and make it a 30 to 70 year
>break up??.
>If my question is not clear it is as follows:
>The Oracle's default format of date of two digit is 'RR' type and any
>date less that 50 years from today it will be considered THIS century
>and any date more than 50 years from today it will be considered the
>NEXT century. Can I change this format to 70 and 30 year format instead
>of 50-50??
>I will really appreciate some help here.
>my e-mail address is aamirza_at_uno.edu
>Thankx
>azam
No, you cannot change the window upon which RR works.
You can use SQL to fix you data up tho. Lets say you were using sqlldr to load data. Assuming you were loading data in the form YYMMDD and wanted years 00-29 to be 2000-2029 and years 30-99 to be 1930-1999, you could use a control file like this:
LOAD DATA
INFILE *
INTO TABLE T
REPLACE
( d position(1:6)
"decode( sign(to_number(substr(:d,1,2))-30),
-1, to_date( '20'||:d,'yyyymmdd'),
to_date( '19'||:d,'yyyymmdd'))",
input position(1:6)
that loads the character string into a date field and does the edit you want. it also loads the original raw data so we can compare our results as follows:
SQL> select to_char(d,'dd-mon-yyyy'), input from t;
TO_CHAR(D,' INPUT
----------- ------------------------- 01-mar-2025 250301 01-jan-2029 290101 20-oct-1930 301020 20-oct-1931 311020 12-jan-1999 990112
7 rows selected.
If not using SQLLDR, you would use the DECODE statement above in your own INSERT statement (instead of just inserting "insert into t values ( :myString )" and letting the date mask convert the data, you will "insert into t values ( decode(.....) )" and explicitly to_date the data in the decode)....
--
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 Wed Jul 28 1999 - 06:34:35 CDT
 |
 |