[RBG7-L] - Re: Change DateTime into Date

[Claudine Robbins]

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. *****************************************

<<image001.gif>>