0

I have a SQL Server database. One of my tables has a SQL DATETIME column. This column should not have to be entered when the record is created, but later should be modified.

I understand that DateTime is not nullable. The default for datetime2 is 1/1/0001, while the minimum value for SQL Server is 1/1/1753.

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

So, my question is what is the correct way to do this? How do I set po.PODate to the minimum for SQL Server (1/1/1753).

ApplicationDbContext db = new ApplicationDbContext();

PurchaseOrder po = new PurchaseOrder();
po.PODate = new DateTime(1753, 1, 1);    // Best way?
// po.PODate = DateTime.MinValue;  Doesn't work because DateTime.MinValue is 1/1/0001
// po.PODate = null;               Doesn't work because DateTime is non-nullable value type
// po.PODate = DBNull.value;       Doesn't work because it is for type System.DBNull not DateTime

db.PurchaseOrders.Add(po);
db.SaveChanges();
Community
  • 1
  • 1
Martin Morales
  • 48
  • 6
  • 1
    Why you don't use `DateTime.Now` when the record is created? – Cristian Szpisjak Apr 14 '17 at 17:28
  • I don't want the default value for PODate to be the current time, I want it to be some "ridiculous" DateTime that should never happen. Later on, the time will be changed to a realistic DateTime. – Martin Morales Apr 14 '17 at 17:55
  • 1
    Then just modify you property to accept null. `DateTime PODate { get; set; }` to `DateTime? PODate { get; set; }`. Providing false (or ridiculous) data is not a best practice. If you don't need the data set it as `NULL` and update its value later on. – Cristian Szpisjak Apr 14 '17 at 17:59
  • `DATETIME` in SQL Server ***is*** nullable - no problem at all. And `DATETIME2(n)` has a date range of years 0001 through 9999 - the 1/1/1753 **only** applies to `DATETIME` (which is one of the reasons one should start phasing that type out and use `DATETIME2(n)` instead) – marc_s Apr 14 '17 at 19:46

1 Answers1

0

If you expect the date to be not nullable, then yes, what you have is the best way. EF will always pass in a value to that field regardless, so if you don't, it will try to pass in 1/1/0001 and error.

Rather than have to define it each time, you could create a static class with:

public static DateTime MinDate()
{
   return new DateTime(1753, 1, 1);
}

If you wanted to standardize it.

The other option is make the column nullable, and anytime you query the date out, have SQL return 1/1/1753 when null.

Brian Mains
  • 50,520
  • 35
  • 148
  • 257
  • Hmm, I only use it in one location so I don't think it's worth creating the static class. But, yes that is what I was looking for if I were to be using it in a lot of places. For the nullable thing, I did try overriding the method OnModelCreating in IdentityModel.ApplicationDbContext (but it didn't do anything) like so: ` modelBuilder.Entity().Property(m => m.PODate).IsOptional(); base.OnModelCreating(modelBuilder);` – Martin Morales Apr 14 '17 at 17:59
  • If you were trying the nullable thing; then you can change the type to "DateTime?". If it's DateTime I don't know that IsOptional() will help, but potentially, you could use that to set it to 1/1/1753 maybe? – Brian Mains Apr 14 '17 at 19:07