Question
How can ı convert datetime to smalldatetime in c# ? I'm taking the date and ı need to convert it to be accordance with database. It is forbidden to change the datatype of column in sql.
Accepted Answer
You can use the .NET DateTime type for your entity framework model, but tell EF that it uses a non-default column type in the database. You do this by overriding the OnModelCreating method of your DbContext, and using the HasColumnType method:
public class Foo
{
public int Id { get; set; }
public DateTime IAmSoSmall { get; set; } // wants to be smalldatetime in SQL
}
public class MyContext : DbContext
{
public DbSet<Foo> Foos { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
var foo = modelBuilder.Entity<Foo>();
foo.Property(f => f.IAmSoSmall).HasColumnType("smalldatetime");
base.OnModelCreating(modelBuilder);
}
}
Of course, you'll have to do the appropriate range-checking on your DateTime property to be sure that the stored values fall between those supported by SQL's smalldatetime. I guess you could do that with a property attribute like:
[Range(typeof(DateTime), "1/1/1900", "6/6/2079")]
public DateTime IAmSoSmall { get; set; } // wants to be smalldatetime in SQL
...based on a valid range from January 1, 1900, through June 6, 2079, as documented on MSDN.
Popular Answer
Sql Server datetime and smalldatetime are both automatically mapped to and from the CLR's System.DateTime. A smalldatetime has a precision of 1 minute; a datetime has a precision of approximately 1/300 of a second (Don't ask why. It just is). Since the CLR's System.DateTime1 has a precision of 100-nanoseconds, the runtime takes care of rounding.
smalldatetimeis internally a 32-bit integer, containing a count of minutes since thesmalldatetimeepoch (1900-01-01 00:00).In conversion, seconds and fractional seconds are rounded using SQL Server's arcane date/time rounding rules, so the date 2013-01-31 23:59:59 gets rounded to the next date 2013-02-01 00:00:00'.
datetimeis a pair of 32-bit integers internally. The high-order word is a count of days since the epoch; the low-order word is a count of milliseconds since start-of-day (00:00:00). The epoch of adatetimeis 1900-01-01 00:00:00.000.And again, values are rounded in the conversion in the same arcane way, with franctional seconds getting placed into one of the appropriate millisecond buckets for SQL Server, a multiple of 3ms — there is no SQL Server `datetime value like 2013-05-01 13:57:23.004. That will get "rounded" to either 23.003ms or 23.006ms.
If you want more control over things, you'll need to adjust your datetime values in C# before sending them to the database.
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
