I just did a quick test, and Date can be text, provided that the date
format is identical to the text column.
R>show date
DATE format MM/DD/YYYY
DATE sequence MMDDYY
Century threshold YEAR is 30
Default CENTURY is 19
R>SET VAR vDateText TEXT = '06/30/2014'
R>SET VAR vDate DATE = .vDateText
R>show var vdate%
Variable =
Value Type
-------------------------------- ------------------------------
-------
vDateText =
06/30/2014 TEXT
vDate =
06/30/2014 DATE
On 3/27/2014 8:47 AM, Dennis McGrath wrote:
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
