Would the date format in Excel not be set up to reflect the local layout?
It's a long time since I used Excel and I was never more than a dabbler but
I would have expected the date format in almost any program to be
representative of the local custom - as R:Base always has done.
In other words, there should only be need to change the sequence of day,
month and year when converting dates to or from a different local custom
area.
I'm sure that Gunnar will chime in if I'm wrong but I think in Sweden (and
other Scandinavian countries?) they use year-month-day - which is much more
sensible in many ways.
Regards,
Alastair.
----- Original Message -----
From: "Albert Berry" <[EMAIL PROTECTED]>
To: "RBASE-L Mailing List" <[email protected]>
Sent: Saturday, January 12, 2008 4:37 AM
Subject: [RBASE-L] - Re: DATETIME String Conversion ...
Mike B just pointed out that that works fine up here, and for Alastair
Burt, but not for you folks South of the 49th. I'll think on this and
maybe come up with something to flip the month/day.
Albert Berry wrote:
I was thinking more along the lines of an import routine that would
translate the column. If you have 17 columns, and #8 is the datetime
column, you could create an import table of 17 columns, and run an update
against the import table, then just import it. R:Base will recognize the
modified value and import it. You could create a stored procedure along
these lines to do the work.
CREATE TEMP TABLE ImportTable (...)
LOAD ImportTable FROM whatever ... or
INSERT INTO ImportTable SELECT ... or ...
UPDATE ImportTable SET DateTimeColumn =
(SRPL(DateTimeColumn,"Jan","01",0))
UPDATE ImportTable SET DateTimeColumn =
(SRPL(DateTimeColumn,"Feb","02",0))
UPDATE ImportTable SET DateTimeColumn =
(SRPL(DateTimeColumn,"Mar","03",0))
UPDATE ImportTable SET DateTimeColumn =
(SRPL(DateTimeColumn,"Apr","04",0))
UPDATE ImportTable SET DateTimeColumn =
(SRPL(DateTimeColumn,"May","05",0))
UPDATE ImportTable SET DateTimeColumn =
(SRPL(DateTimeColumn,"Jun","06",0))
UPDATE ImportTable SET DateTimeColumn =
(SRPL(DateTimeColumn,"Jul","07",0))
UPDATE ImportTable SET DateTimeColumn =
(SRPL(DateTimeColumn,"Aug","08",0))
UPDATE ImportTable SET DateTimeColumn =
(SRPL(DateTimeColumn,"Sep","09",0))
UPDATE ImportTable SET DateTimeColumn =
(SRPL(DateTimeColumn,"Oct","10",0))
UPDATE ImportTable SET DateTimeColumn =
(SRPL(DateTimeColumn,"Nov","11",0))
UPDATE ImportTable SET DateTimeColumn =
(SRPL(DateTimeColumn,"Dec","12",0))
INSERT INTO RealTable SELECT .. FROM ImportTable
SELECT COUNT(*) INTO ImportCount from ImportTable
SELECT COUNT(*) INTO RealCount FROM RealTable
IF ImportCount <> RealCount THEN
-- ERROR ROUTINE
ELSE
DROP TABLE ImportTable
ENDIF
You could get really fancy and nest your SRPLs, but probably would not
gain much.
Many more years ago than I care to think, I used to import a text file
printed to disk, and performed a whole pile of manipulations, and then
put it into the real data set.
James Bentley wrote:
Albert,
An interesting take but not easy to incorporate into a table
without a performance hit. I am surprised that Rbase accepted
the "-" instead of the "/" as delimiters.
Jim Bentley
--- Albert Berry <[EMAIL PROTECTED]> wrote:
R:Base after R:azzak is surprisingly smart.
SET VAR vDateTime DATETIME
SET VAR vDateText TEXT
SET VAR vDateText = '03-Oct-2007 01:23:38 PM'
SET VAR vDateText = (SRPL(.vDateText,"Oct","10",0))
set var vDateTime = .vDateText
SHOW VAR vDate%
vDateTime = 03/10/2007 13:23:38 DATETIME
vDateText = 03-10-2007 01:23:38 PM TEXT All
you need to do is import the date from the other db as
text, then do 12 SRPLs and the problem disappears. I was interested to
notice that the PM was recognized by R:Base.
Albert
Wills, Joseph S 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
____________________________________________________________________________________
Never miss a thing. Make Yahoo your home page.
http://www.yahoo.com/r/hs
--
No virus found in this incoming message.
Checked by AVG Free Edition. Version: 7.5.516 / Virus Database:
269.19.0/1218 - Release Date: 10/01/2008 13:32
