How do I convert a SQL Server datetime to a C# int64 in ADO.NET?

I would like to read from a datetime field in SQL Server and store it as a int64, as datetime is stored as 64 bits in SQL Server. 开发者_StackOverflow社区Would I do something similar to this?

DateTime dt = sqlDataReader.GetDateTime(0);
byte[] bt = BitConverter.GetBytes(dt);
// unfortunately, GetBytes() does not take DateTime as an argument
long ldt = (Convert.ToInt64(bt[0]) << 56)
         + (Convert.ToInt64(bt[1]) << 48)
         + (Convert.ToInt64(bt[2]) << 40)
         + (Convert.ToInt64(bt[3]) << 32)
         + (Convert.ToInt64(bt[4]) << 24)
         + (Convert.ToInt64(bt[5]) << 16)
         + (Convert.ToInt64(bt[6]) << 8)
         + (Convert.ToInt64(bt[7]));

Get the date value as a unixtimestamp-style INT directly from SQL server:

select datediff(second, {d'1970-01-01'}, yourdatefield) as seconds

It'll save you all the bit fiddling in your app.

If you want to implement a mysql-style "unix_timestamp()" function in tsql, this blog entry shows how to create a function for it: http://skinn3r.wordpress.com/2009/01/26/t-sql-datetime-to-unix-timestamp/

Why not dt.ToBinary()?

Using System.GC.GetTotalMemory, it seems that DateTime uses 212 bytes while long uses 8 bytes.