Converting DateTime to SmallDateTime in c#

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.

Related: http://stackoverflow.com/questions/14124887/datetime-from-net-to-smalldatetime-in-sql-how-to-do-queries — George Johnston, Jun 05 '13 at 16:30
Please show us the relevant code. You say you are converting, but do you mean in a where clause? During deserialization into your entity? Something else? — Matt Johnson-Pint, Jun 05 '13 at 16:37
And since this is down-converting, you would do well to explain what you want from values that press the edges of smalldatetime boundary. — DonBoitnott, Jun 05 '13 at 16:38
@MattJohnston I'm using entity framework. I guess I need to write a trigger to solve this problem. — kapozade, Jun 05 '13 at 17:05

score 19 · Accepted Answer · answered Jun 05 '13 at 17:42

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.

You can also change the column type using the data annotation `[Column(TypeName="smalldatetime")]`. See http://stackoverflow.com/a/4833477/1180926 — Arithmomaniac, Sep 15 '14 at 13:28
@Arithmomaniac Yes, it looks like as of EF 4.3.1 the column type data annotation works. Nice. — Myk Willis, Sep 15 '14 at 17:50

score 9 · Answer 2 · answered Jun 05 '13 at 17:15

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.

Eh? SQL Server counts time in increments of 0.000, 0.003 or 0.007 seconds for no apparent reason. Always has since the Sybase days. Even then, under Windows NT, both BIOS and the WinNT system clock had a resolution c. 10ms. Sybase must have implemented their own time implementation using the CMOS "real-time" clock, bypassing BIOS. That chip ticked at something like 32.768 kHz to make binary arithmetic easy. Why they'd hammer that into unevenly sized buckets that don't mesh well with either a decimal values or it's binary representation. I'm sure it made sense to somebody at the time. — Nicholas Carey, Jun 05 '13 at 20:45
I totally misread that the first time as if you were saying there was no rational for the different precisions of `datetime` and `smalldatetime`, not catching on to the fact that your paranthetical referred to the arbitrary 1/300 s precision of `datetime`. Sheepishly removing my misguided comment now. — jball, Jun 05 '13 at 21:20

score -1 · Answer 3 · answered Jun 05 '13 at 16:30

-1

Maybe you can do something like YourDateTime.Date

Normally, when you do that way, it will set time to 00:00:00.

answered Jun 05 '13 at 16:30

user2335149

75
2
12

`smalldatetime` *can* represent a time component. See [the docs](http://msdn.microsoft.com/en-us/library/ms182418.aspx) – Matt Johnson-Pint Jun 05 '13 at 16:38

Converting DateTime to SmallDateTime in c#

3 Answers3