Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to switch to datetime2 in Entity Framework / SQL Server 2008 project

Tags:

We have an Entity Framework 5.0 project with code-first migrations with SQL Server 2008 but all date properties were created in the database as datetime columns - not datetime2.

Is it possible to create migration using add-migration that will update all datetime columns in the database? Is there any other easy way to switch to datetime2 everywhere?

like image 340
mykola Avatar asked Mar 28 '13 16:03

mykola


People also ask

How insert Datetime2 value in SQL Server?

Always use the format YYYY-MM-DD hh:mm:ss[. nnnnnnn] to insert the date into database. This is the default format that SQL Server uses. It is also the safe format and can be interpreted only in one way.

What is the difference between datetime and Datetime2?

The main difference is the way of data storage: while in Datetime type, the date comes first and then time, in Datetime2, 3 bytes, in the end, represents date part!

How do I change the date format in Entity Framework?

cshtml page is using @Html. EditorFor . Then the attribute needs to be DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyInEditMode=true)] . Note the format string - it will generate the browsers implementation of a datepicker and display it in the culture of the browser.

What is SQL Datetime2?

Defines a date that is combined with a time of day that is based on 24-hour clock. datetime2 can be considered as an extension of the existing datetime type that has a larger date range, a larger default fractional precision, and optional user-specified precision.


2 Answers

This is an old post, but if you want to switch ALL your datetime columns to datetime2, and use datetime2 for any new columns you add (in other words, make EF use datetime2 by default), you can add this to the OnModelCreating method on your context:

modelBuilder.Properties<DateTime>().Configure(c => c.HasColumnType("datetime2"));

That will get all the DateTime and DateTime? properties on all the entities in your model.

like image 129
Mark Shapiro Avatar answered Oct 04 '22 04:10

Mark Shapiro


You can use fluent API to force creating datetime2 columns in DB.

I found this:

Using DateTime properties in Code-First Entity Framework and SQL Server

You should be able to get the idea.

Or, if you have to stick with the existing DB then you should be able to create a migration that executes custom T-SQL code. There is an example here:

http://msdn.microsoft.com/en-us/data/jj591621.aspx

like image 41
Floremin Avatar answered Oct 04 '22 03:10

Floremin