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.
6 Answers
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', ..);
7 Comments
Ozi Oz
The issue is, that if the input is empty, then it shouldn't put '1900-0-0-0 etc' but NULL, and i shouldn't have to put all those conditions then
Mahmoud Gamal
@OziOz See my edit. Make it
DEFAULT NULL and igonre it in the insert list, other wise if you didn't used DEFAULT NULL, make it nullable and insert null in that columnOzi Oz
I'm sorry Mahmud i don't understand you that well :( Where do i put this? should i create a New Query for this? DateTimeColumn DateTime DEFAULT NULL,
Mahmoud Gamal
@OziOz If this datetime fild is defined as
DateTimeColumn DateTime DEFAULT NULL, just ignore it in the insert columns' list and it will be inserted with NULL value.Ozi Oz
Aha... ok, i got it :) Thanks!
|
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.
Comments
- define it like
your_field DATETIME NULL DEFAULT NULL - dont insert a blank string, insert a NULL
INSERT INTO x(your_field)VALUES(NULL)
Comments
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.
Comments
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.
1 Comment
CodeCaster
"DATE and DATETIME fields don't behave in the same way as VARCHAR" - that's because an empty, non-null string (
"") is a valid string, but it's not possible to construct a valid, empty, non-null date.you can use like this:
string Log_In_Val = (Convert.ToString(attenObj.Log_In) == "" ? "Null" + "," : "'" + Convert.ToString(attenObj.Log_In) + "',");
if there is no value inserted, the default value should be null,emptyThen why not make it Nullable, and insertNULL?INSERTstatement?