FP 2.5 erratic Select SQL results when using date fields 
Author Message
 FP 2.5 erratic Select SQL results when using date fields

Hello,

I've been having a lot of trouble lately with Foxpro 2.5 no longer
being able to consistently or accurately interpert date fields in
querys.

Example: A query I've used for years is no longer working

SELECT DISTINCT inv.qty, inv.item, inv.title, inv.date;
 FROM inv;
 WHERE inv.date > DATE()-60;
   AND inv.date < DATE()-18;
 ORDER BY inv.title

Even though there are plenty of records within the date range, I now
get an empty output.  If I manually figure out what today's date less
60 days and less 18 days respectively are, and use the actual dates I
get the proper results.  It seems to be {*filter*} on the function
date()-X

The query hasn't been modified in ages, and I've tried running it with
both "Set Century" on and off, but neither makes a difference.  I have
no idea why it stopped working.  It always used to be fine.

This behavior isn't resticted to just this query.  I have others that
use date fields in comparisons, and they no longer work either.  I've
scanned for viruses, and I'm clean (not that I really thought a virus
was the problem.)

Any ideas?  Is it poor coding on my part?  Is there a better way to do
it?  I'm stumped.

Thanks,
Jim



Sat, 15 Jan 2005 05:00:18 GMT  
 FP 2.5 erratic Select SQL results when using date fields
I'd first try to rule out Y2K problems.  Write a loop that will scan through
your data files, look for date types, and flag any that are about a century
old.  And then get Y2KFox.

 - Rush


Quote:
> Hello,

> I've been having a lot of trouble lately with Foxpro 2.5 no longer
> being able to consistently or accurately interpert date fields in
> querys.

> Example: A query I've used for years is no longer working

> SELECT DISTINCT inv.qty, inv.item, inv.title, inv.date;
>  FROM inv;
>  WHERE inv.date > DATE()-60;
>    AND inv.date < DATE()-18;
>  ORDER BY inv.title

> Even though there are plenty of records within the date range, I now
> get an empty output.  If I manually figure out what today's date less
> 60 days and less 18 days respectively are, and use the actual dates I
> get the proper results.  It seems to be {*filter*} on the function
> date()-X

> The query hasn't been modified in ages, and I've tried running it with
> both "Set Century" on and off, but neither makes a difference.  I have
> no idea why it stopped working.  It always used to be fine.

> This behavior isn't resticted to just this query.  I have others that
> use date fields in comparisons, and they no longer work either.  I've
> scanned for viruses, and I'm clean (not that I really thought a virus
> was the problem.)

> Any ideas?  Is it poor coding on my part?  Is there a better way to do
> it?  I'm stumped.

> Thanks,
> Jim



Sat, 15 Jan 2005 05:24:45 GMT  
 FP 2.5 erratic Select SQL results when using date fields
Thanks for the tip.  Some recently imported backup data must have been
imported with century off, and thus now are 100 years off.  I issued a
replace command on those two tables to bring all the dates back to
this century and everything is working again.  As for Y2kFox, at $499
it's not worth it to the home user like me when a simple workaround
like I found gets me back on track.

Thanks again for your help,
Jim

Quote:

> I'd first try to rule out Y2K problems.  Write a loop that will scan through
> your data files, look for date types, and flag any that are about a century
> old.  And then get Y2KFox.

>  - Rush



Sat, 15 Jan 2005 23:52:13 GMT  
 FP 2.5 erratic Select SQL results when using date fields
Glad to help.  As it happens, I've been working on a legacy 2.0 system that
had such date bugs.  But a warning: setting century on or off won't affect
date imports if the imported date has a two digit year - Fox will still
prepend a '19'.

But $499 for Y2KFox?  Was that a typo?  The price is $99.

 - Rush


Quote:
> Thanks for the tip.  Some recently imported backup data must have been
> imported with century off, and thus now are 100 years off.  I issued a
> replace command on those two tables to bring all the dates back to
> this century and everything is working again.  As for Y2kFox, at $499
> it's not worth it to the home user like me when a simple workaround
> like I found gets me back on track.

> Thanks again for your help,
> Jim




Quote:
> > I'd first try to rule out Y2K problems.  Write a loop that will scan
through
> > your data files, look for date types, and flag any that are about a
century
> > old.  And then get Y2KFox.

> >  - Rush



Sun, 16 Jan 2005 00:18:21 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. results: Filtering no-match record/field (SELECT-SQL)

2. Different results with SELECT SQL and DELETE SQL.

3. Selecting/Inserting date values from within a string - FoxPro 2.5

4. FP W 2.5 Memo Fields in Report Writer Printing Only 4 Lines

5. Filtering results by Date Fields

6. 2.0 to 2.5 conversion problem in SQL Select

7. Odd 2.5 select-sql problem

8. Select - SQL without a result set

9. Indexing with FP 2.0 from FP 2.5

10. what settings can effect results of SELECT SQL

11. SELECT - SQL result set dependant on whether table is indexed

12. SELECT SQL return wrong result

 

 
Powered by phpBB® Forum Software