Joseph,
Ask and you shall receive. Try the following in v7.6 or v8.0 in
any earlier versions you will needed to split up the computed
column definition as it is too long.
-- TO CONVERT EXCEL FORMATED DATETIME VALUE 'DD-MMM-YYYY
HH:MM:SS AP'
-- TO RBASE DATETIME VALUE. USE A TEMPORARY OR PERMANENT TABLE
WITH
-- A COMPUTED COLUMN
CREATE TEMP TABLE CNVTDATETIME (ExcelDt +
TEXT (23),RBDateTime=(DATETIME(RDATE((INT(SGET(ExcelDt,2,1))),+
(INT((SLOC('
JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC',(SGET(ExcelDt,3,+
4))))/3)),(INT(SGET(ExcelDt,4,8)))),RTIME((IFEQ((SGET(ExcelDt,2,22)),'PM',+
12,0))+(INT(SGET(ExcelDt,2,13))),(INT(SGET(ExcelDt,2,16))),+
(INT(SGET(ExcelDt,2,19)))))) DATETIME )
INSERT INTO CNVTDATETIME (ExcelDt) VALUES ('03-OCT-2007 01:23:38
PM')
RETURN
Please note that there are 3 spaces in fromt of "JAN"
Jim Bentley
American Celiac Society
--- "Wills, Joseph S" <[EMAIL PROTECTED]> wrote:
> This one is challenging me. I want to take this formatted
> DATETIME
> value, '03-Oct-2007 01:23:38 PM', a string from EXCEL, and
> convert it to
> some RBase DATETIME value or separate DATE and TIME values. I
> know that
> I can do RDATE||RTIME(INT_1,INT_2,INT_3), but that 3-CHAR
> abbreviation
> for MONTH has me stymied.
>
> The source is a Production DB in Oracle, to which I don't yet
> have any
> access, so it's probably gonna' be difficult in the short run
> for me to
> fix this by swimming upstream.
>
> Any thoughts?
>
> Thanks,
> Steve in Memphis
>
>
> J. Stephen Wills
> Program Manager, Research Informatics
> Office of the Vice Chancellor for Research
> University of Tennessee Health Science Center
> 62 S. Dunlap, Suite 400
> Memphis, TN 38163
> Office: 901-448-2389
> FAX : 901-448-7133
>
>
>
Jim Bentley
American Celiac Society
[EMAIL PROTECTED]
tel: 1-504-737-3293
____________________________________________________________________________________
Looking for last minute shopping deals?
Find them fast with Yahoo! Search.
http://tools.search.yahoo.com/newsearch/category.php?category=shopping
