|Subject:||Re: Qbe date parse results - final?
|Date:||Thu, 29 Mar 2018 11:15:47 +1000
|From:||Bernie van't Hof <firstname.lastname@example.org>
The only outstanding issue is '...'. Is that 'dot'-'dotdot' or 'dotdot'-'dot'. One for another
DD and MM padded where appropriate so just one set of sql format strings used consistently.
1 and 2 digit YY are century converted. YY = 00 => 2000.
No surprises I can see in the 600 odd test cases thrown at it. No more "too hard for now'.
On 29/3/18 5:46 am, Bernie van't Hof wrote:
> 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,
> More to do (read buggy) but headed in the right direction:
> SELECT DISTINCT
> FROM `transact`
> 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)
> - Bernie
> 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