Paradox Community

Items in pnews.paradox-dos

Subject:Re: Qbe date parse results - final?
Date:Thu, 29 Mar 2018 05:46:53 +1000
From:Bernie van't Hof <>
Removed the horrible wild-date juggling code and implemented DATE_FORMAT in WHERE per Larry's

As predicted, sorting out the sizes of date parts is tricky and also matching the correct format
string type as there 
are multiple types for each part. 2 or 4-dig yr, 01-31 or 1-31 day, 01-12 or 1-12 mon, etc

More to do (read buggy) but headed in the right direction:

FROM `transact`
WHERE DATE_FORMAT(`Date`, '%e-%c-%Y') LIKE '%-Feb-2015'

WHERE DATE_FORMAT(`Date`, '%e-%c-%Y') LIKE '3._%.2015'

WHERE DATE_FORMAT(`Date`, '%e-%c-%Y') LIKE '3.2%'

WHERE DATE_FORMAT(`Date`, '%e-%c-%Y') LIKE '%2' (DD-Mon-YY)
WHERE DATE_FORMAT(`Date`, '%c-%e-%Y') LIKE '%2' (MM/DD/YY)
WHERE DATE_FORMAT(`Date`, '%e-%c-%Y') LIKE '%2' (DD.MM.YY)
WHERE DATE_FORMAT(`Date`, '%Y-%c-%e') LIKE '%2' (YY.MM.DD)

- Bernie

On 15/3/18 4:40 am, Larry DiGiovanni wrote:
> Bernie,
> I've been out of this for quite a while, but I just now looked at the latest code (briefly)
and (skimmed) the last 
> couple hundred posts here.
> Paradox date wildcard matching in QBE seems rooted in pattern matching the date using string
comparison of the input 
> wildcard against a string representation of the date using only the CCP date format in
effect.  I know this for a fact 
> because I just did two whole tests using one CCP default format.  :-)
> So given a CCP format of MM/DD/YY and a QBE wildcard of 2/../18, it appears I can closely
approximate QBE wildcard 
> behavior with;
> select * from sometable where date_format(somedatecolumn, '%m/%d/%y') like '02/%/18';
> I see that there'd be some fiddling with the incoming wildcard text.  For instance, I
had to LPAD 0 above, and 
> interpreted the .. as wildcard and / as separator.  It also results in arguably poorer
performing SQL, since casting the 
> date probably defeats index use.
> Feel free to mock me as appropriate.  I am assuming my confusion is because I just haven't
done my homework.
> -- 
> Larry DiGiovanni

Copyright © 2004