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

Question

I'm using Entity Framework in Visual Studio 2012 developing C# program. I want to add records in my database table . The record (object) includes an attribute (TRANSACTION_DATE) which doesn't allow NULL values and it's a DateTime format. In database i'm aiming for this format:

yyyy-MM-dd HH:mm:ss.fff

so i want to pass the current date and time to it, my code is like this:

newEntry.TRASACTION_DATE = DateTime.ParseExact(DateTime.Now.ToString(), 
                            "yyyy-MM-dd HH:mm:ss.fff", CultureInfo.InvariantCulture);

but it's giving me error:

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

I want to know why it's not converting to the format i want?

constraints:

Answer 1

Can you switch the column type in your database to datetime2? This is Microsoft's recommendation. Some .Net datetime values don't fit into datetime, whereas the datetime2 type can hold all .Net datetimes (and won't introduce subtle bugs by, for example, rounding 2010-05-05 23:59:59.999 to 2010-05-06).

Note that in contrast to datetime, it is not legal to treat datetime2 as an integer (e.g., by doing stuff like mydate+1).

If switching to datetime2 is not an option, configure your ORM code to explicitly treat this date as datetime rather than datetime2. This won't fix your error, but it should cause validation to fail before you send your query rather than afterwards, making it easier to fix.

Regardless of your choice, do not use conversion to/from strings to resolve this problem; that's a hack that causes more problems than it solves.

Answer 2

You shouldn't think about the database as storing the values in a particular string format at all - any more than it stores numbers as decimal or hex.

Instead, you should just view it as a date/time, e.g.

// TODO: Do you really want the local time, rather than UtcNow?
// TODO: Change TRANSACT_DATE to be TransactionDate ideally, to follow
// .NET naming conventions
newEntry.TRANSACT_DATE = DateTime.Now;

When you retrieve the value, you should get a DateTime at that point too. Then if you want to display the value to the user you can apply a particular format. Different users may well want different formats - and may even want to display the same date/time in different time zones.

It's important to differentiate between the intrinsic data you store (in this case a date/time) and the text format that happens to be used to display it any one particular context/application. You should avoid conversions to/from strings at any time when you don't really need one. Ideally, these should only be at the boundaries of your application - e.g. when displaying text to a user, or potentially serializing to JSON or XML. Whenever an API allows you to not perform a conversion (e.g. with a database parameter) you should avoid it.

As for your current error - is it possible that it's a different field which you're not populating, and which is therefore using default(DateTime), which would be out of range? That would make a lot of sense - whereas DateTime.Now really shouldn't be out of range, unless you've either applied a separate constraint, or your system clock is miles out.

Answer 3

DateTime is irrespective of the format. Presentation format like the one you have is just for displaying purpose. There is no need to convert DateTime to string and then parsing it with a custom format. Just simply assign DateTime to your field like:

newEntry.TRASACTION_DATE = DateTime.Now;

DateTime in SQL Server has a range of January 1, 1753, through December 31, 9999 Your parsing code is resulting a DateTime value less than year 1753 and that is why you are getting the exception.