DateTime saving in Database from code

Question

I am having problem saving date value in the database using EF.

In my C# code my dates are defined as:

DateTime ed = DateTime.Now.AddMonths(+6);

While in SQL Server the date column is defined as DateTime. Dates are sorted in YYYY/MM/DD format with no time.

When I try to insert values in the database with the following code:

t_a m = new t_a();
m.dbDate = DateTime.Today;
entity.AddTot_a(m);
entity.SaveChanges();

I get the following error message:

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

I believe it is because the value is showing as MM/DD/YYY: HH:MM:SS, while in the database it saves as YYY/DD/MM. How can I solve this issue?

@HanletEscaño he is already. He says he runs the code in the question and it result in an error. — Geeky Guy, Jun 05 '13 at 22:03
Following post had same error but I can not implement this solution http://stackoverflow.com/questions/1331779/c-sharp-conversion-of-a-datetime2-data-type-to-a-datetime-data-type — J. Davidson, Jun 05 '13 at 22:09

marc_s · Accepted Answer · 2013-06-06T05:06:52.963

SQL Server doesn't store a DateTime in any string format - it's stored as an 8 byte numerical value. So the string formatting cannot be the problem.

Also: you're using .NET DateTime all the way - that's the way to do it, so the cause of this error must be something else.

Guessing from the error message, I think you might have this situation:

you're developing your EF model against a SQL Server 2008 (or 2008 R2, or 2012) version database
you're running your application against a SQL Server 2005 database

The problem is: if you use the EF modelling tools against a 2008 (or newer) database, it will default to using DATETIME2 as the "date" datatype - but SQL Server 2005 doesn't have that datatype.

What you need to do is make sure that before you build your application, the .edmx file is opened as an XML file in Visual Studio (right-click on it in Solution Explorer, use "Open with..." and pick the XML editor), and then you find and fix this attribute in the EDMX file:

<Schema Namespace="MdsModel.Store" Alias="Self" Provider="System.Data.SqlClient" 
        ProviderManifestToken="2005" ....>
        ****************************

Make sure the ProviderManifestToken is set to a value of 2005.

Unfortunately, I haven't found a really good way to set this as a property anywhere, you just have to check this every time to change something in your EDMX model file and before you build your app.

score 0 · Answer 2 · answered Jun 05 '13 at 22:20

0

For some reason it won't let me add a comment (sorry, still kind of new to this site) so i post this as answer: Could using the SqlDateTime data type be an option for you?

answered Jun 05 '13 at 22:20

Seb

23
1
6

DateTime saving in Database from code

2 Answers2