Dates and defaults

Hi,
We have been working with SQL 2000 for about a year now pretty successfully
except with dates. This just doesn't seem to be correct, we came for the
VFP data world and it worked with dates easier however dates/times only
recently became one so that was always a fun calculation. In the SQL data
table design tools we can set default values for varchar's, bit and float
fields just to name a few but we cannot set a default value for dates so
they are not posted with a null or with a silly date of 1899 or 1900.
We simply want to be able to store a blank value in a date field - for
example, we have a payment received field with the date/time. A customer
has not been paid yet so the date/time field should be blank or empty yet it
forces us to have an erroneous date. When we import data we need to stuff
fields with that bogus date of 1900. I'm sure we have just missed something
so please point us in the correct direction.
Thank you!
ChrisYou can't have an empty datetime like you did in VFP. You need to allow
NULL's and store a NULL instead.
Andrew J. Kelly SQL MVP
"Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
news:eq308JSJFHA.1948@.TK2MSFTNGP14.phx.gbl...
> Hi,
> We have been working with SQL 2000 for about a year now pretty
> successfully except with dates. This just doesn't seem to be correct, we
> came for the VFP data world and it worked with dates easier however
> dates/times only recently became one so that was always a fun calculation.
> In the SQL data table design tools we can set default values for
> varchar's, bit and float fields just to name a few but we cannot set a
> default value for dates so they are not posted with a null or with a silly
> date of 1899 or 1900.
> We simply want to be able to store a blank value in a date field - for
> example, we have a payment received field with the date/time. A customer
> has not been paid yet so the date/time field should be blank or empty yet
> it forces us to have an erroneous date. When we import data we need to
> stuff fields with that bogus date of 1900. I'm sure we have just missed
> something so please point us in the correct direction.
> Thank you!
> Chris
>|||Either set a default date (a fixed value or based on the the
CURRENT_TIMESTAMP) or allow the value to default to NULL. I don't understand
why you say you can't set a default for the date. What is the problem? Do yo
u
get an error message?
Unlike VFP, SQL Server doesn't have a blank date other than NULL. That
shouldn't really be a problem. Is there some functionality you want that you
can't get from a NULL or another default date value?
David Portas
SQL Server MVP
--