RE: Date conversion question
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: "Harvinder Singh" <Harvinder.Singh@xxxxxxxxxxxxx>, "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 3 Oct 2007 17:18:05 -0400
Ok, this shouldn't be too difficult. I'm not going to give you the complete
answer, but here's a good start:
Here's the setup, using your test data:
SQL> create table date_conv(input_date number(6), ora_date date);
Table created.
SQL> insert into date_conv values(991123,null);
1 row created.
SQL> insert into date_conv values(421,null);
1 row created.
SQL> insert into date_conv values(60823,null);
1 row created.
SQL> insert into date_conv values(0,null);
1 row created.
SQL> insert into date_conv values(70930,null);
1 row created.
SQL> commit;
Commit complete.
Ok, now, check it out, this looks like the input data:
SQL> select * from date_conv;
INPUT_DATE ORA_DATE
---------- ---------
991123
421
60823
0
70930
From that, we can get it zero-padded:
SQL> select lpad(to_char(input_date),6,'0') from date_conv;
LPAD(T
------
991123
000421
060823
000000
070930
And here, we've got it parsed out into YY MM DD. You'll need to translate some
stuff, based on your special cases, but this ought to get you started pointing
in the right direction:
SQL> select substr(lpad(to_char(input_date),6,'0'),1,2) yy,
substr(lpad(to_char(input_date),6,'0'),3,2) mm,
substr(lpad(to_char(input_date),6,'0'),5,2) dd from date_conv;
YY MM DD
-- -- --
99 11 23
00 04 21
06 08 23
00 00 00
07 09 30
Hope that helps,
-Mark
--
Mark J. Bobak
Senior Database Administrator, System & Product Technologies
ProQuest
789 E. Eisenhower, Parkway, P.O. Box 1346
Ann Arbor MI 48106-1346
+1.734.997.4059 or +1.800.521.0600 x 4059
mark.bobak@xxxxxxxxxxxxxxx
www.proquest.com
www.csa.com
ProQuest...Start here.
-----Original Message-----
From: Harvinder Singh [mailto:Harvinder.Singh@xxxxxxxxxxxxx]
Sent: Wednesday, October 03, 2007 5:04 PM
To: Bobak, Mark; oracle-l
Subject: RE: Date conversion question
"0 = 1 jan 1900
1 = 1 jan 1900"
It looks inconsistent but that is the requirement since moving legacy data and
it is clearly defined in the requirement.
"Also, 2-digit year? Is 02 supposed to map to 2002 or 1902??"
If the year is specified then
00 to 07 means 2000 to 2007
08 to 99 means 1908 to 1999
And if the year is not specified then default is 1900
Thanks
--Harvinder
-----Original Message-----
From: Bobak, Mark [mailto:Mark.Bobak@xxxxxxxxxxxxxxx]
Sent: Wednesday, October 03, 2007 4:57 PM
To: Harvinder Singh; oracle-l
Subject: RE: Date conversion question
Hmm...so:
0 = 1 jan 1900
1 = 1 jan 1900
??
Seems inconsistent to me.
Also, 2-digit year? Is 02 supposed to map to 2002 or 1902??
I think you need to better understand your current dataset before attempting to
map it into Oracle.
Once you have a clear understanding, including all the corner cases, the data
conversion should be the trivial part.
-Mark
--
Mark J. Bobak
Senior Database Administrator, System & Product Technologies
ProQuest
789 E. Eisenhower, Parkway, P.O. Box 1346
Ann Arbor MI 48106-1346
+1.734.997.4059 or +1.800.521.0600 x 4059
mark.bobak@xxxxxxxxxxxxxxx
www.proquest.com
www.csa.com
ProQuest...Start here.
-----Original Message-----
From: Harvinder Singh [mailto:Harvinder.Singh@xxxxxxxxxxxxx]
Sent: Wednesday, October 03, 2007 4:47 PM
To: Bobak, Mark; oracle-l
Subject: RE: Date conversion question
Data is in format "yymmdd", for example
991123 (23 nov 1999)
421 (21 apr 1900)
60823 (23 aug 2006)
0 (1 jan 1900)
70930 (30 sep 2007)
Default Year 1900
Default Month 1
Default Day 1
-----Original Message-----
From: Bobak, Mark [mailto:Mark.Bobak@xxxxxxxxxxxxxxx]
Sent: Wednesday, October 03, 2007 4:37 PM
To: Harvinder Singh; oracle-l
Subject: RE: Date conversion question
Well, that depends on what those values actually represent...
Are they julian dates? Days/minutes/seconds since some epoch?
How would you convert 991123? What date does that represent? How about 421?
0?
Without knowing what the data means, we have no way to answer you....
-Mark
--
Mark J. Bobak
Senior Database Administrator, System & Product Technologies
ProQuest
789 E. Eisenhower, Parkway, P.O. Box 1346
Ann Arbor MI 48106-1346
+1.734.997.4059 or +1.800.521.0600 x 4059
mark.bobak@xxxxxxxxxxxxxxx
www.proquest.com
www.csa.com
ProQuest...Start here.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Harvinder Singh
Sent: Wednesday, October 03, 2007 3:44 PM
To: oracle-l
Subject: Date conversion question
Hi,
We have a flat file that contains a column and the values are stored as:
991123
421
60823
0
70930
Now we are moving data from flat file to table and this column will be
inserted into Date dataype column, What is the way to insert into this
table and avoid "ORA-01821: date format not recognized" errors.
Thanks
--Harvinder
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
