I get a "Function Argument value, type, or count is invalid " error on the following:
SELECT 12, 2006, rec_tp, district, site, l1,gf,sf,fid,be,ibi,cat,yr, ;
cfi, swdn, pdn, sdn, src,;
DATETIME(VAL(RIGHT(ytd_disb.audt_dt,4)), VAL(LEFT(ytd_disb.audt_dt,2)), VAL(SUBSTR(ytd_disb.audt_dt,3,2))) as mach_dt2,;
DATETIME(VAL(RIGHT(ytd_disb.mach_dt,4)), VAL(LEFT(ytd_disb.mach_dt,2)), VAL(SUBSTR(ytd_disb.mach_dt,3,2))) as audt_dt2,;
glc, tran_tp, cmp_amt, ann_desc, DATEtime() as fld1 ;
FROM ytd_disb;
WHERE src='C'
Now here's the kicker:
1. By taking out the Datetime(val(....)) fields. It will run the query.
2. By running the Datetime(val(....)) fields OUTSIDE the query against the field they work individually.
What's wrong with this?
All fields from the source table are character including the audt_dt and mach_dt fields. These two are simply dates as characters in the form: MMDDYYYY.
TIA,
Richard
State of Florida, DCF