Archive | January, 1999

Empty Dates and Client-Server

(Reprinted from FoxPro Advisor, January 1999)
■ Visual FoxPro 6.0/5.0/3.0
Q: When a Visual FoxPro table has an empty date, the Upsizing Wizard puts January 1, 1900 in the SQL Server table. Can this be overridden?
—Benjamin A. Aminnia (via CompuServe’s VFox forum)

A: You can’t override how the Upsizing Wizard handles your tables, but consider replacing the empty values in the table with .NULL.; ODBC and most other data sources have no equivalent for the “empty” value that FoxPro stores. Most ODBC data sources have a default value of {^1900-01-01} (sometimes {^1899-12-31} or {^1899-12-30}, depending on the product and whether they counted leap years correctly).
So, to get blank dates represented properly in other data formats, you need to use the .NULL. value. You need to modify the code within your system to handle null values by using the ISNULL() and NVL() functions.
NVL() is a clever little function that makes handling nulls much easier. It’s like a built-in:

IIF(ISNULL(YourVariable) , NewValue, YourVariable)

—you can shorten that to

NVL(YourVariable, NewValue)

—Ted

Powered by WordPress. Designed by Woo Themes

This work by Ted Roche is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 3.0 United States.