Converting DateTime to SmallDateTime in c#

asp.net c# entity-framework

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.

1
4
6/5/2013 4:25:36 PM

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.

9
6/5/2013 5:42:45 PM

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.

  • smalldatetime is internally a 32-bit integer, containing a count of minutes since the smalldatetime epoch (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'.

  • datetime is 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 a datetime is 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.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow