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

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: enter image description here

enter image description here

Jon Skeet
people
quotationmark

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.

people

See more on this question at Stackoverflow