Hi Emmitt, When I checked the AS FORMATTED option, I also read about the AS ASCII option which doesn�t report such errors as truncating errors and loaded the data back in a couple of seconds.
So, the process to make it work was: 01. Create a temporary table and change the field name and the definition to TEXT (8) 02. Output the data from the permanent table SET NULL -0-; OUT data.dta; UNLOAD DATA FROM table USING ALL AS ASCII; OUT SCREEN 03. Load the data to the temporary table LOAD MyTable FROM data.dta AS ASCII. It truncates to 8 characters and obviates the need to SRPL the extraneous stuff� Thanks to everyone who responded. Claudine :) ________________________________________ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Emmitt Dove Sent: Tuesday, July 27, 2004 8:29 AM To: RBG7-L Mailing List Subject: [RBG7-L] - Re: Change DateTime into Date Claudine, Have you tried the LOAD ... AS FORMATTED option? Mel and Paula, I am having a hard time doing this although it makes perfect sense.� I have created a temporary table as Paula describes with new field names of TEXT definition (I have three fields to convert).� When I attempt to load the temporary table with the data I output from the permanent table, I get truncating data errors and the data won t load.� There are just over 28,000 rows. I ll give it another try tomorrow with a smaller test set. ________________________________________ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Paula Stuart Sent: Monday, July 26, 2004 7:04 PM To: RBG7-L Mailing List Subject: [RBG7-L] - Re: Change DateTime into Date � Claudine � �I do a lot of data conversions....and this is a common issue. � Create a temporary table with all fields the same as the permanent table, but use a different name for the column with the DateTIME data.� Define that column as TEXT.� Then you can use the SRPL or SGET commands to strip the times off the end.� � THEN change the definition of that column to DATE and the same name as you will use for this date column in the permanent table. � Append to the permanent table.� All should be well. � Paula -------Original Message------- � From: [EMAIL PROTECTED] Date: 07/26/04 19:26:54 To: RBG7-L Mailing List Subject: [RBG7-L] - Re: Change DateTime into Date � Thanks Bill.� Indeed, I can do it in Excel.� Guess I was hoping there was a way to do it in Rbase :( � -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Bill Downall Sent: Monday, July 26, 2004 6:17 PM To: RBG7-L Mailing List Subject: [RBG7-L] - Re: Change DateTime into Date � Claudine, � DEXTRACT(datetimevalue) is a way to get just the date from a datetime. � If all the datetimes in the export file have all zeros for the hours, minutes and seconds, you can do a global replace in a text editor of every occurance of " 0:00:00" with a blank, before you do the loading. � Bill � On 26 Jul 2004 at 18:01, Claudine Robbins wrote: � > That 'blank' Oracle database I'm converting to Rbase has every date > field defined as DATETIME.� I want to change all those fields to DATE > values. SRPL says that DATETIME cannot be used in that function (I > tried to either blank out the time or extract the date). > > I basically have all the dates formatted something like 01/01/04 > 0:00:00. When I export the values, I get '01/01/04 0:00:00'.� I > replace the 0:00:00 with null and I still have '01/01/04�� ' which > gives an error when reloading in the table where the fields are now > defined as DATE. > � -- Bill Downall [EMAIL PROTECTED] 317.297.3810 � ******************************** Notice: The ISP for our domain, SBC Ameritech, is currently having a problem keeping up with the volume of email and spam going through their mail servers. This means that sometimes mail gets through to and from us immediately, and sometimes the SBC Ameritech web hosting mail servers get constipated, so that delivery of mail to or from downallconsulting.com can be delayed by as much as half a day. If your message is urgent, please follow up with a telephone call. ***************************************** � � � � Emmitt Dove Manager, DairyPak Business Systems Blue Ridge Paper Products, Inc. 40 Lindeman Drive Trumbull, CT� 06611 (203) 673-2231 [EMAIL PROTECTED] [EMAIL PROTECTED]
