Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Anders Altberg
  Where is Anders Altberg?
 Uppsala
 Sweden
 Anders Altberg
 To: Jim Carls
  Where is Jim Carls?
 Nashville
 Tennessee - United States
 Jim Carls
 Tags
Subject: RE: Invalid date/datetime value in SELECT
Thread ID: 227884 Message ID: 228681 # Views: 3 # Ratings: 0
Version: Visual FoxPro 9 Category: Databases, Tables and SQL Server
Date: Wednesday, May 20, 2009 7:16:45 PM         
   


> I have a local view that is used in a form and in a report called by that form (in a private data session). The view includes a number of fields created by comparing status dates using functions like "empty(mytable.shipped)" or "date()+3 > mytable.promised". My problem is that on one particular data set, I keep getting an error 2034 "Date/datetime evaluated to an invalid value" from the OpenTables method when I try to run the report. However, the form that uses the view displays the data in a grid without a problem and if I run the view's SELECT statement from the command prompt, the result is displayed and I cannot see any problems in any of the date field columns.
>
> If I replace the data set but keep the view DBC, no problem occurs, so it must be something in the data that is triggering the bug. I first thought that my syntax might have caused a problem with empty dates, such as (using example above) "date() > mytable.promised-3" but changing it did not make a difference. My question is: Could there be something more subtle for me to check to find the cause? Thanks!

An expression like DATE()>mytable.promised+3 won't work if the promised date is empty or something's made the recordpointer go to EOF().

-Anders

ENTIRE THREAD

Invalid date/datetime value in SELECT Posted by Jim Carls @ 5/13/2009 8:55:46 PM
RE: Invalid date/datetime value in SELECT Posted by Tamar Granor @ 5/13/2009 10:00:19 PM
RE: Invalid date/datetime value in SELECT Posted by Jim Carls @ 5/14/2009 12:56:31 AM
RE: Invalid date/datetime value in SELECT Posted by Jim Carls @ 5/15/2009 3:47:24 PM
RE: Invalid date/datetime value in SELECT Posted by Tamar Granor @ 5/15/2009 10:27:03 PM
RE: Invalid date/datetime value in SELECT Posted by Jim Carls @ 5/20/2009 6:50:43 PM
RE: Invalid date/datetime value in SELECT Posted by tushar @ 5/20/2009 7:38:20 PM
RE: Invalid date/datetime value in SELECT Posted by Anders Altberg @ 5/20/2009 7:16:45 PM