When your system settings are yyyy-mm-dd, save a copy of your DB as Clone and that calculation will work.
While saving a copy as a clone causes the clone to function properly, it does not address the issue with my production database. I simply cannot start from scratch every "n" months. I have 65 tables with1000's and 1000's of records in this database.
You should only have to do this fix once.
Perhaps you can set up a script to import all your data into the clone and set it to run overnight or something.
I have no confidence that the problem will not happen again. The database has been running for almost 11 months, with the date function working properly. It has been running on a server, so it has no direct user interaction, yet suddenly it stops working. While I have tried the clone and have seen that the date function works again, why would I believe it will continue to work? If I was "changing" something so the problem will not re-create itself I could understand, but simply rebuilding the entire database and trying again seem like just wishful thinking. Am I missing something here?
Am I missing something here?
Probably - but then so are we. FWIW, I canot reproduce the issue, and it doesn't make sense anyway: if the file knows how to get "2010-05-03" as a valid date, there's no reason why "2010-06-03" should pose a problem. I'd suspect some corruption has occured.
This issue sounds related to this known bug:
I believe that's why Daniele recommended this fix.
Edit note: and you don't have to "rebuild your entire solution" just save a copy as a clone (no records) and import your data back into the clone. This can be done with a script so that you don't have to chain yourself to the computer and stare at progress bars all day. If you can run this when the database is not being accessed and changed, you can run the scripts and swap files pretty easily.
I agree with Comment: "I'd suspect some corruption has occured."
A corrupted index will be rebuilt by the import into a clone and this might be what you are dealing with here. You could try just rebuilding the index on that field--which can be done without making a new copy of the file.
Thanks for the help.
I will build a script to move the data over to the clone.
Personally I don't understand (and I hate when I don't understand), how this is happening. I can simply open the Data Viewer in the production database and type GetAsDate("2010-06-03") - evaluate now and get ?, yet the same query in the clone works? No tables, relationships or anything involved, just a simple function.
I can simply open the Data Viewer in the production database and type GetAsDate("2010-06-03") - evaluate now and get ?, yet the same query in the clone works? No tables, relationships or anything involved, just a simple function.
That would be consistent with the production file using a date format other that YYYY-MM-DD. But then you would get the same error with ANY date in this format - not just dates after June 1 2010.
It is sure that my english isn't good but I understand that his DB started to fail on June 1 2010, not that an earlier date NOW works.
Well, if that's true, then all they need to do is switch the file options back to "Always use current system settings" and investigate who/what caused the change.
I have cloned the database and re-imported the data - and the database is again operational.
Let me explain setup for this solution:
There is an application database that is distributed to users, and there are 2 repository databases that sit on a server. One of these repository databases just houses forms, while the other houses to data for this solution. Part of the role of the data DB is to go to the internet every morning and download the current foreign exchange (FX) rates. These rates are then parsed and added to an historical FX table.
When a users creates a new order in the system and it involves multiple currencies, the system gets the most current FX rate from the FX historical table and applies it to the order.
This system has been in use since August 2009.
This week I started to notice some new orders with NO FX rates. When I went to the FX historical table, all the entries up to June were good, but for June the date fields all contained "?". When I looked at the script that imports the FX entries I determined that the GetAsDate function was returning "?" instead of the date. Even using the Data Viewer and just entering GetAsDate("2010-06-02") returned ?
As recommended on the board, I created a clone of the database. I ran the FX import script and everything was fine. Last night I ran a script to import the data from the production database to the clone and now I'm running again.
For the record, the File Option are "Always use current system settings", and have not been changed. In fact the server hasn't been touched or rebooted for over 30 days. I'd really like to understand how this problem evolved so I can avoid it in the future.