Paradox Community

Items in pnews.paradox-dos

Subject:Re: Qbe date parse results - final?
Date:Thu, 29 Mar 2018 11:15:47 +1000
From:Bernie van't Hof <berniev@bje.com.au>
Newsgroups:pnews.paradox-dos
UPDATE:  Done!

The only outstanding issue is '...'. Is that 'dot'-'dotdot' or 'dotdot'-'dot'. One for another
day. Maybe.

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'.

- Bernie
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
suggestion.
> 
> 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:
> 
> ..-Feb-2015
> SELECT DISTINCT
> `Date`
> FROM `transact`
> WHERE DATE_FORMAT(`Date`, '%e-%c-%Y') LIKE '%-Feb-2015'
> ORDER BY `Date`
> 
> 3.@...2015
> WHERE DATE_FORMAT(`Date`, '%e-%c-%Y') LIKE '3._%.2015'
> 
> 3.2..
> WHERE DATE_FORMAT(`Date`, '%e-%c-%Y') LIKE '3.2%'
> 
> ..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 thedbcommunity.com