What is the prefered way to handle NULL DATE values with a SQL backend

[jrumbaug]

I am using VFP 8.0. I am developing a client-server application. The server is MSDE. I have determined that the upsizing wizard does not like my empty date fields. I have learned that the large date range is from about 1756 to 9999. I get the idea that 01/01/1901 is the most common default value used for a NULL field. I want to know: is there a better date or standard used for null fields. For example: even though unlikly, I may run into a 106 year old client needint 1900. I have wondered if the date value of 09/09/9999 has been used as a standard. Please make suggestions and quide me.

Jim Rumbaugh

 

[MarciaAkins]

I have learned that the large date range is from about 1756 to 9999. I get the idea that 01/01/1901 is the most common default value used for a NULL field.

I am sorry, but I must have taken my stupid pills today because I do not understand what the problem is. SQL Server does not support EMPTY dates, so what I would do if I were you would be to modify the structure of my VFP tables and allow the date fields to be null. Then I would replace all the empty dates with NULL - problem solved.

Or is there somehing that I am not understanding?



Marcia G. Akins

http://www.TightlineComputers.com

 

[Mike Lewis]

Jim,

Unless you are dealing with data from the 19th century or earlier, I suggest you use Smalldatetime rather than Datetime. Its range is 1 Jan 1900 to 6 June 2079, with a precision of one minute. It takes half storage of Datetime. The usual default is 1 Jan 1900, but if your application can tolerate NULLs, you should follow Marcia's advice and use NULLs instead.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[jrumbaug]

Thank you Marcia and Mike,
I have learned something today. I now know the difference between a blank date field and a NULL date field. Previously, I thought they were equal. I went to the TABLE DESIGNER and put a check mark on the NULL property by each DATE FIELD. I was then able to successfully use the upsizing wizard. I consider this problem solved.

Thanks again,

Jim Rumbaugh