----- Original Message -----
From: "Alastair Burr" <[EMAIL PROTECTED]>
To: "RBASE-L Mailing List" <[email protected]>
Sent: Saturday, January 12, 2008 2:33 AM
Subject: [RBASE-L] - Re: DATETIME String Conversion ...
> Would the date format in Excel not be set up to reflect the local layout?
Locale or Custom because of formatting functions, just like most DBMS.
> 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
>>
>>
>
>
>
