DateTime Fields (Defaulted to '')

[benasumwa]

Hi Friends

I am trying to learn SQL Server. I have noted that
if I create a DateTime field (on SQL Database) allowing no nulls with default as ''. My SELECT statement requesting
this field fills the VFP cursor with "01/01/1900 12.00.00PM"
value.

1. Is it a SQL bug?
2. What do I need to do to make sure it returns
empty for empty and values for values?

I am learning use MSDE 2000 with VFP6

Benson

 

[Mike Lewis]

Benson,

No, it's not a bug.

The problem is that SQL Server does not have the concept of blank dates. In fact, blank dates (that is " / / ") are a peculiarity of the xBase languages (which includes FoxPro).

The value "01/01/1900 12.00.00PM" was probably inserted by the VFP upsizing wizard. There's nothing special about that date as far as SQL Server is concerned.

The usual solution is to allow the date column to support NULLs. I know this is a nuisance, but it's easier to test for IS NULL that for an arbitrary date like "01/01/1900 12.00.00PM". Also, NULLs are preferable to fictitious dates that have no meaning within the data.

Set your date columns to nullable, and don't bother with a default constraint.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[benasumwa]

Mike

Thanks. In my reading, I have had guys standing
strongly against nulls saying you have to be sure
you really need them. I think in my case, as you put it
I need them. ISNULL will help me out.

Thanks Mike you have been a saviour to many.


Benson