CODEWITHSUNDEEP
sql
Ozi Oz
Ozi Oz

Reputation: 289

Insert null/empty value in sql datetime column by default

How do I create a table in SQL server with the default DateTime as empty, not 1900-01-01 00:00:00.000 that I get?

I mean, if there is no value inserted, the default value should be null, empty, etc.

Upvotes: 18

Views: 315263

Answers (6)

Matt
Matt

Reputation: 1358

Even if your column is nullable, inserting an empty string will set the value to 1900-01-01 00:00:00.000. Make you insert real nulls not strings, then the db will store a null.

Upvotes: 2

Masud Sikder
Masud Sikder

Reputation: 1

you can use like this:

string Log_In_Val = (Convert.ToString(attenObj.Log_In) == "" ? "Null" + "," : "'" + Convert.ToString(attenObj.Log_In) + "',");

Upvotes: 0

zDavid
zDavid

Reputation: 1

I was having the same issue this morning. It appears that for a DATE or DATETIME field, an empty value cannot be inserted. I got around this by first checking for an empty value (mydate = "") and if it was empty setting mydate = "NULL" before insert.

The DATE and DATETIME fields don't behave in the same way as VARCHAR fields.

Upvotes: 0

Uberbot7147
Uberbot7147

Reputation: 49

Ozi, when you create a new datetime object as in datetime foo = new datetime(); foo is constructed with the time datetime.minvalue() in building a parameterized query, you could check to see if the values entered are equal to datetime.minvalue()

-Just a side thought. seems you have things working.

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

if there is no value inserted, the default value should be null,empty

In the table definition, make this datetime column allows null, be not defining NOT NULL:

...
DateTimeColumn DateTime,
...

I HAVE ALLOWED NULL VARIABLES THOUGH.

Then , just insert NULL in this column:

INSERT INTO Table(name, datetimeColumn, ...)
VALUES('foo bar', NULL, ..);

Or, you can make use of the DEFAULT constaints:

...
DateTimeColumn DateTime DEFAULT NULL,
...

Then you can ignore it completely in the INSERT statement and it will be inserted withe the NULL value:

INSERT INTO Table(name, ...)
VALUES('foo bar', ..);

Upvotes: 27

silly
silly

Reputation: 7887

  1. define it like your_field DATETIME NULL DEFAULT NULL
  2. dont insert a blank string, insert a NULL INSERT INTO x(your_field)VALUES(NULL)

Upvotes: 0

Related Questions