Empty Dates into SQL

Question

Post reply

Empty Dates into SQL

Mauricio Morales S

Ten Centuries

Points: 1300
More actions
September 6, 2005 at 3:48 pm

#88812

There some questions here about the utilization of Empty dates into SQL.
I have one Table where one of the Fields is Type DateTime, this Field have a restriction of Not Null.
CREATE TABLE [TABLE_NAME] (
[FIELD_CHAR] [char] (5) NOT NULL ,
[FIELD_DATE] [datetime] NOT NULL ,
CONSTRAINT [PK_TABLE_NAME] PRIMARY KEY CLUSTERED
(
[FIELD_CHAR]
&nbsp ON [PRIMARY]
) ON [PRIMARY]
GO
When I INSERT some data into this TABÑE from FOX PRO the Date Field in Fox Pro has a value called Empty, this data Updates SQL and the data is represented into SQL as:
1900-01-01 00:00:00.000
How I can avoid that? Without make nulleable the field 'FIELD_DATE'.
Maybe there is some data type callled Empty in SQL also?

Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ray M SSC-Insane Points: 21093 More actions · Answer 1

No, there is no "Empty" value for a date in sql.

Null means not available, or not defined.

If you do not allow NULLS, then you must supply a date.

You can set up a default on the column for a specific date, or getdate().

but its my guess that when your populating the date from foxpro it is supplying the 01/01/1900 value.

Sergiy SSC Guru Points: 110206 More actions · Answer 2

Yes, there is, they name it "NULL".

_____________
Code for TallyGenerator

SQLBill SSC Guru Points: 51440 More actions · Answer 3

As everyone else said....if you set the column for NOT NULL, then you must provide a datetime. If you don't provide a datetime, then the default is used. If you don't provide a default, then the 'built-in' datetime default is used and that is what you are seeing. 1900-01-01 00:00:00.000 is the built-in default.

-SQLBill

Lowell SSC Guru Points: 323506 More actions · Answer 4

if you pass zero to a date time field, it is a valid date. that date is 1900-01-01 00:00:00.000 of course, so i think your foxpro database has zeros for a number of your date fields you are playing with.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!