-1

I had a datetime column in a SQL Server table. We needed to encrypt it. So, I cast it to a varbinary in SSMS. I simply edited the table, set the datetime type to varbinary and let SQL Server convert it. Following that, I wrote throwaway C# code to pull it out, encrypt it (using the encryption algorithms in our middle layer) and push it back into the database. Did the same for some nvarchars for names and other string types.

When I pull out the encrypted data (using NHibernate), I pull the varbinary into a byte[] and decrypt it. I then try to convert it back to the original value.

The nvarchar-as-varbinary columns convert fine; for example, I get may names back.

return Encoding.Unicode.GetString(source.FirstName);

However, I'm having a hard time converting the dates back into their original form. I'm using:

long ticks = BitConverter.ToInt64(source.DateOfBirth, 0);
return new DateTime?(new DateTime(1980, 1, 1).AddMilliseconds(ticks));

This does not seem to return the date properly. What's the correct way to cast it back to a DateTime?

Update: A sample value was requested. A datetime that was originally 1/1/1901, when decrypted, yields a byte[] where byte[2]=1 and byte[3]=109, and all others are 0. Another datetime '1941-04-26' yields byte[2]=58 and byte[3]=242 upon decryption.

alphadogg
  • 12,762
  • 9
  • 54
  • 88
  • Do you have any examples of the binary values, and what kind of dates you expect? – Guffa Jan 18 '13 at 01:44
  • Hard to guess. What is special about 1980? DateTime starts at the year 0. SQL Server already supports encryption, best not to reinvent that wheel: http://blog.sqlauthority.com/2009/04/28/sql-server-introduction-to-sql-server-encryption-and-symmetric-key-encryption-tutorial-with-script/ – Hans Passant Jan 18 '13 at 01:58
  • @Guffa: Well, they are encrypted varbinaries so I'm not sure you can tell much from example values? The approach I took works fine for nvarchars that I cast to varbinary in SQL Server. However, I'm having a hard time getting the values from what were datetimes. – alphadogg Jan 18 '13 at 02:06
  • @Hans: I'll look into that (For example, we want to move to Amazon's RDS. Does it support that approach?). However, is there an answer to my question? Seems like it should work, but my code may be off in some subtle way I am missing. – alphadogg Jan 18 '13 at 02:24
  • @alphadogg: I'm sure you got the decryption to work, so seeing the values after that step could help to determine what kind of format the database converted the datetime values to when you converted the fields. – Guffa Jan 18 '13 at 02:28
  • Sure would be useful for downvoters to say why they hate. SO seems to be a game for some... – alphadogg Jan 18 '13 at 03:03

3 Answers3

1

The date is stored as the number of days since 1900-01-01.

Example:

byte[] data = {0,0,58,242};

if (BitConverter.IsLittleEndian) {
  Array.Reverse(data);
}
int days = BitConverter.ToInt32(data, 0);
DateTime date = new DateTime(1900, 1, 1).AddDays(days);

Console.WriteLine(date);

Output:

1941-04-26 00:00:00
Guffa
  • 687,336
  • 108
  • 737
  • 1,005
  • That's it. The only modification I made was that, in my case, data was a `byte[16]` so I used `int days = BitConverter.ToInt32(data, data.length-4);` to make sure I got the right four bytes. Thanks a bunch. Glad you got that in before the SO close police got to this post. You saved me quite a bit of time. – alphadogg Jan 18 '13 at 13:33
0
Int64 ticks = BitConverter.ToInt64(source.DateOfBirth, 0);
DateTime dt = DateTime.FromBinary(ticks);
Tommaso Belluzzo
  • 23,232
  • 8
  • 74
  • 98
0

After you decrypt the varbinary data, can you get the string value and run it through SSMS? Something like:

SELECT CONVERT(datetime, 0x0000A149016D7FD9)

What does your varbinary data look like in SQL Server? I suspect something is being messed up in translations.

Good luck.

sgeddes
  • 62,311
  • 6
  • 61
  • 83