Thanks Troy, I will try that. Phil
-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Troy Sosamon Sent: Tuesday, February 12, 2002 12:38 AM To: [EMAIL PROTECTED] Subject: RE: A faster way is appreciated In R:base you could set the date format and sequence to yyyymmdd like this: R>set date yyyymmdd Now you need to deal with your 99999999 dates and make your valid dates. I assume you are loading this into R:base as a text field. I would just do an update like this: R> update newtable set col1 = '99991231' where col1 = '99999999' Now you have col1 which is text full of valid dates, but it is still text. Add a field to the table of type date (date1) and then just stuff the text into the date col and you should be good to go: R> update newtable set date1 = col1 This method should be a lot faster than having to convert these one row at a time. I would also drop any indexes off of the columns you are updating if there are any before you do it and then put the indexes back when you are done. If you then want to dump the data out with a format of yyyy-mm-dd, just change your date format and dump out the new date col. set date yyyy-mm-dd Troy Sosamon >===== Original Message From [EMAIL PROTECTED] ===== >I am faced with a date problem that was not in the intial design. The date >was going to be defaulted to the conversion date but the value in the row >will now have to be converted in 94,000 rows of data of which there are nine >columns with date values which is being passed as eight characters >(YYYYMMDD). >I have created a routine that will be used to parse and convert the date >prior to writing the record. (Not all records will be converted and not in >their original format - they are being made into load transactions for >SQLOADER). > >I have tested this application and it works but I am concerned about time. >Can anyone see a faster way? >--------------------------------------------------------------------------- - >----- >-- Program: DCONV.RMD >-- Purpose: Receives an eight character representation of a date (YYYYMMDD) >and formats it to a date variable + >-- for loading into an Oracle date format (YYYY-MM-DD) >-- RBase Date Setting is: MMDDYYYY > >clear all variables > >-- This variable will be passed from the calling program >set var vin_date text = '99999999' > >-- Parse the 8 position text value for date >SET VAR vyr = (SGET(.vin_date,4,1)) >SET VAR vmos = (SGET(.vin_date,2,5)) >SET VAR vday = (SGET(.vin_date,2,7)) > >if vmos = '99' then > set var vmos = '12' >endif >if vday = '99' then > set var vday = '31' >endif > >set var vnewdate2 = (RDATE (INT(.vmos),INT(.vday),INT(.vyr))) > >clear all variables except vnewdate2 >-- This will be passed back to the calling program >write .vnewdate2 > >return > >Phil >[EMAIL PROTECTED] > >================================================ >TO SEE MESSAGE POSTING GUIDELINES: >Send a plain text email to [EMAIL PROTECTED] >In the message body, put just two words: INTRO rbase-l >================================================ >TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] >In the message body, put just two words: UNSUBSCRIBE rbase-l >================================================ >TO SEARCH ARCHIVES: >http://www.mail-archive.com/rbase-l%40sonetmail.com/ Troy Sosamon Denver Co [EMAIL PROTECTED] ================================================ TO SEE MESSAGE POSTING GUIDELINES: Send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: INTRO rbase-l ================================================ TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: UNSUBSCRIBE rbase-l ================================================ TO SEARCH ARCHIVES: http://www.mail-archive.com/rbase-l%40sonetmail.com/ ================================================ TO SEE MESSAGE POSTING GUIDELINES: Send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: INTRO rbase-l ================================================ TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: UNSUBSCRIBE rbase-l ================================================ TO SEARCH ARCHIVES: http://www.mail-archive.com/rbase-l%40sonetmail.com/
