CODEWITHSUNDEEP
c#sqldatetimec#-4.0datetime2
scaryghost
scaryghost

Reputation: 77

Why converting empty value to datetime2 is throwing conversion error in SQL?

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?

Upvotes: 1

Views: 1447

Answers (4)

Serg
Serg

Reputation: 22811

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;

Upvotes: 0

Raushan Kuamr Jha
Raushan Kuamr Jha

Reputation: 463

.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.

Upvotes: 0

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726699

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.

Upvotes: 2

Muzzamil Asad
Muzzamil Asad

Reputation: 3

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

Upvotes: 0

Related Questions