OOPS, This is better! --Convert data string to date UPDATE ImpProcess SET CELLG = (SRPL(CELLG,'/',',',0))
UPDATE ImpProcess SET PaidDate = + (RDATE(INT(SSUB(CELLG,1)),INT(SSUB(CELLG,2)),INT(SSUB(CELLG,3)))) + WHERE CELLG LIKE '%,%,%' Dennis McGrath Software Developer QMI Security Solutions 1661 Glenlake Ave Itasca IL 60143 630-980-8461 [email protected] -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Dennis McGrath Sent: Thursday, March 27, 2014 9:46 AM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Date problem while importing Excel sheet This is what I came up with: --Convert data string to date UPDATE ImpProcess SET CELLG = (SRPL(CELLG,'/',',',0)) UPDATE ImpProcess SET PaidDate = + (RDATE(INT(SSUB(CELLG,1)),INT(SSUB(CELLG,2)),INT(SSUB(CELLG,3)))) + WHERE AND CELLG LIKE '%,%,%' Dennis McGrath Software Developer QMI Security Solutions 1661 Glenlake Ave Itasca IL 60143 630-980-8461 [email protected] -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Buddy Walker Sent: Thursday, March 27, 2014 9:16 AM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Date problem while importing Excel sheet Tony If I remember correctly sometimes Excel adds time to it even though it is set as date only. I would suggest importing into a temp table. In the temp table make sure to have an extra column at the end that is date. If the imported field looks like a date then just update the last column based on the imported date. Your temp table should look sometime like this TempTableName Imp_Col_1 text 8 Imp_Col_2 text 10 - your imported text date field RLastCol DATE >From the R:> or code UPDATE TmpTableName SET RLastCol = Imp_Col_2 Buddy -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Tony IJntema Sent: Thursday, March 27, 2014 9:12 AM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Date problem while importing Excel sheet Buddy, I have done that. The setting was dd-mm-yyyy (European setting) I have imported the spreadsheet after having defined a RGW-file, but the date values were ignored in the import, because they were delivered in a different way. The source of the problem is the poor date capabilities of Excel. Maybe XLSX is working better, but I need to be sure that everybody can use the spreadsheet, thats why I am using XLS. In the meantime I have implemented a little conversion program by myself. I extract the day, month and year value from the text field and then I convert it to a date value using Rdate It works fine and as far as I can see now it is solid like a rock. Thank you for your suggestion. Tony -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Buddy Walker Sent: donderdag 27 maart 2014 13:49 To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Date problem while importing Excel sheet Tony I think this starts with the Excel worksheet. Make sure the column is set as date (mm/dd/yyyy) or whatever matches the database sequence. Not sure how you are doing the import but during import you can select how the date being imported is set. Buddy -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Tony IJntema Sent: Thursday, March 27, 2014 8:39 AM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Date problem while importing Excel sheet Jason, Normally this is what I am doing, but in this case the database is distributed to a lot of people (everybody will have its own database). So I am not in control of the situation. I like to avoid complicated handling at the user site. I have found a solution using the Rdate function, but I was hoping that I missed a conversion function. Tony -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Kramer, Jason J Sent: donderdag 27 maart 2014 13:12 To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Date problem while importing Excel sheet Can you dump to CSV and import that instead? Jason Jason Kramer University Archives and Records Management 002 Pearson Hall (302) 831 - 3127 (voice) (302) 831 - 6903 (fax) -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Tony IJntema Sent: Thursday, March 27, 2014 8:08 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Date problem while importing Excel sheet Hi, In one way or another Excel (xls) is always causing problems when I try to import a date value in a table. To avoid this I can import the date values as a text and then I like to convert the text value into a date value. Does anyone know a solution for converting text values to date values. I cannot find this in the help Tony
