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.
*****************************************
Manager, DairyPak Business Systems
Blue Ridge Paper Products, Inc.
40 Lindeman Drive
Trumbull, CT 06611
(203) 673-2231
[EMAIL PROTECTED]
[EMAIL PROTECTED]
<<inline: image001.gif>>
