SQL DateTime Vs. C# Datetime MinDate

Question

I have a SQL Server database, one of my tables LatestData has a column which is a non-nullable DATETIME, with a default value of 01/01/1970. See code below the the exact T-SQL code for this column.

[MyDateTime] DATETIME DEFAULT (CONVERT([DATETIME], CONVERT([DATE], '1970/01/01 00:00AM', (0)), (0))) NOT NULL

When this table is added into our server code (C#) via an .EDMX data model, the field looks like the following:

public System.DateTime MyDateTime { get; set; }

I add new data to this table through C#, but at the time of adding the row, my MyDateTime column does not have any data.

As the column is Not Null, my MyDateTime field is set to 01/01/0001 automatically.

Trying to add this date to my DateTime column throws the following error:

System.Data.SqlClient.SqlException: The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.

After doing some research I found that C# Datetime MinDate is 01/01/0001, whereas SQL Server DATETIME's mindate is 01/01/1753 and this is causing the error.

The C# code is passing the 'empty' field as 01/01/0001 to the database which is then trying to convert it. This is obviously unsuccessful.

Is there any way for the database to know to revert to the default value instead of trying to convert the DATETIME firstly, or do so if a conversion fails?

I know that I could set the column in the Datatable to be a DateTime2 or specify a date for this column before adding it via C#, but that doesn't seem like the best way to go about it?

Thanks in advance for any help.

Use `datetime2` instead of datetime. There's no `empty` date in C# either. A DateTime is a struct so it *always* has a value. The "zero" value is 1/1/001. That's not empty, that's the default date value. If a date is missing, the entity property and database column should be nullable, ie `DateTime?` and `datetime2(0) NULL`. If you don't want to store time, use `date` in the database — Panagiotis Kanavos, Apr 16 '19 at 11:52
Either make the field nullable or use https://learn.microsoft.com/en-us/dotnet/api/system.data.sqltypes.sqldatetime.minvalue?view=netframework-4.7.2 — Matthew Whited, Apr 16 '19 at 14:30

Caius Jard · Accepted Answer · 2019-04-16T14:24:31.757

If 1970-01-01 is an acceptable no date set to you, then put it in the C#:

public System.DateTime MyDateTime { get; set; } = new DateTime(1970, 1, 1);

I do think you should consider allowing nulls in the column though, and make it:

public System.DateTime? MyDateTime { get; set; }

You asked for a SQLS-only fix; the only one I can think of right now is to use a stored procedure to do your insert:

--set up an example table with a datetime
create table a(a datetime);
--test that an insert doesnt work out
insert into a(a) values(cast('0001-01-01' as datetime2)); --error!
go
--make a procedure to do the insert logic/conversion
CREATE PROCEDURE a_ins(@a DATETIME2) 
AS
BEGIN
  INSERT INTO a (a) 
  --if the date is less than the column will support, default it
  SELECT case when @a < CAST('1753-01-01' as datetime2) THEN cast('1970-01-01' as datetime) else cast(@a as datetime) end
END
GO
--quick run the procedure to test
DECLARE @dt2 DATETIME2 = cast('0001-01-01' as datetime2);
EXEC a_ins @dt2 --inserts 1970

You ca choose what range of dates you want to insert - maybe anything before 1970 will become 1970, maybe anything before 1753, maybe only 0001-01-01 .. you choose in the "case when" logic

Thanks for your answer - after talking with my peers, they have asked for a fix only in the Database/SQL section - do you know if this is possible? — James Tordoff, Apr 16 '19 at 12:57
Not without changing the type of the column, as far as I know.. An INSTEAD OF trigger won't work, because the data supplied cannot be loaded into the virtual table to be used in the trigger, for the same reason that it cannot be loaded into the main table. You could create a stored procedure and have your c# use that, though — Caius Jard, Apr 16 '19 at 14:08

score 1 · Answer 2 · answered Apr 16 '19 at 13:48

This is yet another reason why you should prefer DateTime2 over DateTime - The DateTime2 data type supports the same date range as the .Net framework's DateTime struct - from January 1st 0001 to December 31 9999.

Change the MyDateTime data type to DateTime2, and the default value to 0001-01-01:

ALTER TABLE MyTable
    ALTER COLUMN MyDateTime DateTime2 NOT NULL

To change the default value constraint you need drop it and re-create it. This can be easily done using SSMS (just find it, right-click and drop) but using T-SQL you will have use one of the answers from this post since you didn't specify it's name. Once you've done that, you can add it (with a proper name this time):

ALTER TABLE MyTable
    ADD CONSTRAINT DF_MyDateTime DEFAULT ('0001-01-01') FOR MyDateTime

SQL DateTime Vs. C# Datetime MinDate

2 Answers2