0

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. 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]));
michael
  • 11
  • 2
  • 4
    Why would you do something like that? What's the reason? – Oded Sep 09 '11 at 19:49
  • 1
    Maybe I'm misreading/misunderstanding things but [Int64](http://msdn.microsoft.com/en-us/library/system.int64.aspx) will cost 64 bits and [datetime](http://msdn.microsoft.com/en-us/library/ms187819.aspx) costs 64 bits so ... the gain is what? I mean if it's just an exercise in programming, carry on. – billinkc Sep 09 '11 at 19:54

2 Answers2

0

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/

Marc B
  • 356,200
  • 43
  • 426
  • 500
  • true, but only 32bit unixes. 64bit time_t's are fairly widespread already. And it's easier to do a 32bit->64bit int conversion than it is to do a datetime->int conversion in c# – Marc B Sep 09 '11 at 19:56
0

Why not dt.ToBinary()?

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

jon
  • 41
  • 1