Why when I insert a DateTime null I have "0001-01-01" in SQL Server?

Question

I try to insert the value null (DateTime) in my database for a field typed 'date' but I always get a '0001-01-01'. I don't understand, this field "allow nulls" and I don't know why I have this default value.

I'm using C# asp .net with MVC (Entity Framework), this is my code :

Budget_Synthesis newBS = new Budget_Synthesis
{
    Budget_Code = newBudgetCode,
    Last_Modified_Date = null
};
db.Budget_Synthesis.AddObject(newBS);

Last_Modified_Date is typed System.DateTime? so I don't know why they change this 'null'.

If I try to display the value on my application I get 01/01/0001 00:00:00

And 0001-01-01 with SSMS

Someone can explain me why I can't get a real 'NULL' ?

Best regards

you are inserting a c# value of null datetime into a sqldatetime field? — Green Demon, May 22 '13 at 13:10
How are you inserting. Through an application or sql mgmt studio? — Christer Backlund Høllesli, May 22 '13 at 13:10
sql server cannot even **store** 0001-01-01; the minimum value in sql server is January 1, 1753. Where are you seeing this 0001-01-01, **exactly**? — Marc Gravell, May 22 '13 at 13:12
You could use the sql profiler to see what exactly you are trying to parse into the table — Christer Backlund Høllesli, May 22 '13 at 13:13
@Corak yeah, there's a lot of "maybe" in this question. What we need is more detail (hint Alex! more detail please!) — Marc Gravell, May 22 '13 at 13:14
Re the latest edit: you show some code that adds an object to what looks to be an ORM of some kind. Right; we're getting somewhere... what ORM is this? What is that `AddObject` ? How is this all configured? What you *haven't* told us is anything about how all this gets to the database... — Marc Gravell, May 22 '13 at 13:16
I edit with my C# code. I see '0001-01-01' with SQL Manager and "01/01/0001 00:00:00" if I try to display the data in my app. — Alex, May 22 '13 at 13:17
You should create the Last_Modified_Date to allow nullable value — Deepak.Aggrawal, May 22 '13 at 13:17
@MarcGravell I use EntityFramework so I don't really have the detail of the request but I didn't have this kind of problem before, I always get 'null' for every kind of data except 'date' type. Last_Modified_Date is suppose to accept nullable value because it's linked with my DB where I checked "allow nulls" with SQL Manager. — Alex, May 22 '13 at 13:22
"If I try to display the value on my application I get..." What do you get when you query in SSMS? — John Dewey, May 22 '13 at 13:30
@JohnDewey With `SELECT Last_Modified_Date FROM Budget_Synthesis` I get `0001-01-01` — Alex, May 22 '13 at 13:33

score 11 · Accepted Answer · answered May 22 '13 at 13:10

11

I think this is the value corresponding to the null

answered May 22 '13 at 13:10

Hugo Pedrosa

347
2
12

5

@Andomar which is all well and good, but that isn't what this answer says – Marc Gravell May 22 '13 at 13:13
@MarcGravell: Comment was meant as additional information. If you run `var d = new DateTime(); Console.WriteLine(d);`, it prints `0001-01-01`, which makes me disagree with the downvotes. – Andomar May 22 '13 at 13:17
1

@Andomar yes, I'm well aware of the zero behaviour of datetime; but without a **lot** more context from the OP, this is just a random factoid. Indeed, the code now edited into the question shows use of `null`, which indicates that the type is most likely `DateTime?`. The default of `DateTime?` is *not* 0001-01-01 – Marc Gravell May 22 '13 at 13:18
1

@MarcGravell: Yeah, I presume nHibernate, Linq2Sql or EF converts the `null` to a `DateTime` somewhere, and that ends up as `0001-01-01`. Perhaps the column is marked as non-nullable in EF. In a way `null` corresponds to `0001-01-01`, which is what this answer says. Might not be a stellar answer, but it's from a new user, and I don't feel this deserves downvotes at all. – Andomar May 22 '13 at 13:25

Spaceman · Answer 2 · 2013-05-22T14:10:17.047

If Last_Modified_Date is of type DateTime, you can't have "real null" because DateTime structure - as others already said- is not nullable. So your sample code will not even compile.

If Last_Modified_Date is of type DateTime? (Nullable<DateTime>) your code is correct, but -as @Nikola Dimitroff said in his answer- you can't have "real null" in your database because the default value for DateTime? is 01/01/0001 00:00:00.

The "real null" you are looking for is DBNull.Value, but you can use it only for System.DBNull type; if you assign Last_Modified_Date = DBNull.Value , whatever the type of Last_Modified_Date is, your code will not compile.

score 3 · Answer 3 · edited Oct 12 '15 at 11:13

3

When saying you are trying to put a null DateTime, are you using a Nullable<DateTime> (a.k.a DateTime?) or simply DateTime? The latter is a value type and its default value is precisely 01/01/0001 00:00:00

edited Oct 12 '15 at 11:13

Appulus

18,630
11
38
46

answered May 22 '13 at 13:15

Nikola Dimitroff

6,127
2
25
31

Based on the code sample that assigns `null` to what is *presumably* a date, we know that it cannot be `DateTime`; thus `DateTime?` is the most likely – Marc Gravell May 22 '13 at 13:17
@NikolaDimitroff It's a `System.DateTime?` generated by EntityFramework when I linked my Models with the DB, that's why I can give him a 'null' but I don't knwo why he changes it in this default value. – Alex May 22 '13 at 13:28

score 0 · Answer 4 · answered Aug 09 '21 at 13:01

As for ADO.NET (and at least as of .NET 4.7), passing null (for example using ExecuteNonQuery) is translated to SQL DEFAULT keyword.

This can be verified using SQL Server Profiler.

For example, the following code:

var sqlCommand = new SqlCommand();
sqlCommand.CommandText = "EXEC CreateLog @UserName, @CreatedOn";
sqlCommand.Parameters.AddWithValue("@UserName", "George");
sqlCommand.Parameters.AddWithValue("@CreatedOn", (DateTime?)null);
sqlCommand.ExecuteNonQuery();

is translated to:

exec sp_executesql N'EXEC CreateLog @UserName, @CreatedOn',
    N'@UserName nvarchar(6), @CreatedOn nvarchar(4000)',
    @UserName=N'George',@CreatedOn=default

This should normally just result in the following error:

The parameterized query [...] expects the parameter '@CreatedOn', which was not supplied.

More details can be found in this answer.

score 0 · Answer 5 · answered Dec 26 '22 at 11:11

When u write the migration , be aware that u type defaultValue:null and make DateTime? in the model class also

public DateTime? FieldEx { get; set; }

migrationBuilder.AddColumn<DateTime>(
               name: "FieldEx",
               table: "tableEx",
               type: "datetime2",
               nullable: true,
               defaultValue:null);

score -3 · Answer 6 · edited Mar 19 '21 at 04:25

-3

In python programming language. The return type of the code:

print(type(df['col_name'].get(key='0001-01-01 BC')))
<class 'NoneType'>

It is not null, being more specific it is of NoneType.

edited Mar 19 '21 at 04:25

Nguyễn Văn Phong

13,506
17
39
56

answered Mar 19 '21 at 02:12

GAURAV KUMAR

1
2

Why when I insert a DateTime null I have "0001-01-01" in SQL Server?

6 Answers6

In python programming language. The return type of the code:

Linked