1

In C# I get this value for a datetime2 type: 

{01/01/0001 00:00:00}

Which is because the client is send empty value. In database it throws error:

The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value

@DateGeneral datetime2 = null

in database and procedure.

How do I cover it?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
scaryghost
  • 77
  • 8
  • 1
    Best would be to check for empty (string) values and pass them as NULL to the database to get rid of invalid data as early as possible. Maybe add more context for a specific way to treat your input. – Filburt Feb 29 '20 at 10:43
  • the problem is that it's not coming as a null if sent empty, it's coming 01/01/0001 00:00:00 – scaryghost Feb 29 '20 at 10:44
  • Check the input and set `SqlParameter` value to `DBNull.Value` when a dummy value is detected. – Serg Feb 29 '20 at 10:50

4 Answers4

2

This happens because DateTime is a non-nullable value type. 01/01/0001 00:00:00 is its default value.

DateGeneral, on the other hand, is nullable, which makes the null value in the database incompatible with what you have in C#.

To fix this problem, use a nullable DateTime? data type in your C# program.

Sergey Kalinichenko
  • 714,442
  • 84
  • 1,110
  • 1,523
0

you have to use this to get date of datetime datetime.value.tosrting()

Muzzamil Asad
  • 3
  • 2
0

.Net datetime maps to DateTime2 data-type of SQL server

**Here are the range of these datatypes. ref **

  • DateTime range: 1753-01-01 to 9999-12-31

  • DateTime2 range: 0001-01-01 to 9999-12-31

To make your solution work, here are the probable fixes:

  1. Make the .net datatype as nullable if your property doesn't need any value.

  2. Make the explicit conversion of DataType in .Net (DAL layer) map it to DataTime2 with EF set Column[TypeName = "datetime2"] or in fluent code .HasColumnType("datetime2")

  3. Specify the default value (in case of null from .net) to the acceptable range.

Raushan Kuamr Jha
  • 463
  • 3
  • 9
  • if (GeneralDateI == DateTime.MinValue) { cmd.Parameters.Add("GeneralDate", SqlDbType.DateTime2).Value = DBNull.Value; } – scaryghost Feb 29 '20 at 11:03
  • What if the value is explicitly set to DateTime.MinValue in code repo (somewhere). If you're following this approach then there should be some backing field or flag having information whether it's explicitly set value from code or it's the default value. – Raushan Kuamr Jha Feb 29 '20 at 11:09
0

Sql server DateTime2s Date range is wider then that of DateTime. If you can't change your C# code then add sanitizing code to your proc, kind of

create procedure myproc (.. ,@DateGeneral datetime2 = null, ..)
as
..
-- DATETIME compatiblity check
if (@DateGeneral < DATEFROMPARTS(1753,1,1)) then
   @DateGeneral = null;
Serg
  • 22,285
  • 5
  • 21
  • 48
  • I have done this: if (GeneralDate == DateTime.MinValue) { cmd.Parameters.Add("GeneralDate", SqlDbType.DateTime2).Value = DBNull.Value; } Is this ok? – scaryghost Feb 29 '20 at 11:05
  • Can't say without the context. You may also just skip adding the parameter and Sql server will use default `@DateGeneral datetime2 = null` – Serg Feb 29 '20 at 11:08