Guys, thanks for the help. That 'JAN...DEC' string was slick; wish I'd
thunk of it. Sorry to take so long to reply; I had orientation all day
today.
Jim, I think your calculation :
(INT((SLOC('JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC',(SGET(ExcelDt,3,4))))/
3))
requires a '+2' :
(INT((SLOC('JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC',(SGET(ExcelDt,3,4)) +2
))/3))
or it returns 1 month too early and, in the case of JAN, 0 (zero). I
think this is because SLOC() returns the initial position of the string
that is sought.
Mike, I agree with your thinking on the "one go" possibility. W/re: the
DATETIME_String issue, I am able to successfully create that DATETIME
value by:
> Creating a COMPUTED COLUMN
> SELECT ... INSERT ...
This includes handling the AM||PM value.
However, when I run GATEWAY, I seem to able only to get NULLs in the
DATETIME column.
Very soon I'll probably be doing more reading on DATETIME, especially
the ORACLE-variant, so, if I learn anything useful, I'll share it.
Steve in Memphis
-----Original Message-----
From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of MikeB
Sent: Saturday, January 12, 2008 4:35 PM
To: RBASE-L Mailing List
Subject: [RBASE-L] - Re: DATETIME String Conversion ...
Actually this whole thing could have been done in one go if there was a
function(s) to cast the properly formatted date and time string
representations
to typed data inside of a DATETIME function. I am surprised that
loading the
string values into table works, but can't be cast to datatype directly
in an
expression..
Like
set var vDate Date = '01/01/2007'
will correctly cast to date, but you can't combine two strings inside
DATETIME
set var vDateStr text = '01/01/2007'
set var vTimeStr text = '02:35:07PM'
set var vdate date = .vdatestr
set var vtime = .vtimestr
sho var results:
vDateStr = 01/01/2007 TEXT
vTimeStr = 02:35:07PM TEXT
vdate = 01/01/2007 DATE
vtime = 14:35:07 TIME
BUT
using inderection:
set var vdt DATETIME = (datetime(&vdatestr, &vtimestr))
-ERROR- A left parenthesis cannot be followed by a binary operator.
(2145)
-ERROR- Expression cannot be evaluated. (2179)
and
set var vdt DATETIME = (datetime(.vdatestr, .vtimestr))
-ERROR- Argument 1 of function DATETIME cannot be TEXT. (2153)
-ERROR- Expression cannot be evaluated. (2179)
So I wonder what is used for the logic when a table is loaded?
a Fixer would be like
set var vdt DATETIME = (datetime((strToDate(.vdatestr)),
(strToTime(.vtimestr))))
would guarantee success in an expression.
----- Original Message -----
From: "James Bentley" <[EMAIL PROTECTED]>
To: "RBASE-L Mailing List" <[email protected]>
Sent: Saturday, January 12, 2008 4:42 PM
Subject: [RBASE-L] - Re: DATETIME String Conversion ...
> Mike,
>
> One warning if you are going to mess with date and time formats.
> You need to be the only person on the system for the duration
> of you process as the canges affets all signed on. This could
> have serious unfavorable consequences to any forms that accept
> date input.l
>
> If you check my postings to this thread, it indeed can be done
> in one statement.
>
> Jim Bentley
>
> --- MikeB <[EMAIL PROTECTED]> wrote:
>
>> OK, here is the simple way in one go...
>>
>>
>>
>> {
>> Control via the Date Time Format Seq to match the input
>> Don't mess with functions, they won't convert in one go to
>> DateTime
>>
>> temp table StWills has Three cols:
>> One Date and One Time one Computed to DateTime
>> }
>>
>> SET DATE FORMAT DD-MMM-YYYY
>> SET DATE SEQUENCE ddmmyyyy
>> SET TIME FORMAT HH:MM:SS AP
>> SET TIME SEQUENCE hhmmss
>> SET VAR vstr = '03-Oct-2007 01:23:38 PM'
>> INSERT INTO stwills coldate, coltime SELECT
>> (SGET(.vstr,11,1)),
>> (SGET(.vstr,11,13)) FROM dummy
>>
>>
>> RETURN
>>
>>
>>
>
>
> 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
>
>
>
