|Subject:||Re: Qbe date parse results - final?
|Date:||Thu, 29 Mar 2018 05:46:53 +1000
|From:||Bernie van't Hof <email@example.com>
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:
WHERE DATE_FORMAT(`Date`, '%e-%c-%Y') LIKE '%-Feb-2015'
ORDER BY `Date`
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)
On 15/3/18 4:40 am, Larry DiGiovanni wrote:
> 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