I accidentally used date data type (rather than datetime) to encrypt a column. As I understood from online research, that necessarily entailed conversion to varbinary(200) to begin with, then encrypt.
Now I need to decrypt it:
select convert(varbinary(200), decryptbykey(encrypteddate)) myvarbincol from mytable
.. works fine,
but when I try to convert it back to date:
select convert(date,convert(varbinary(200), decryptbykey(encrypteddate))) mydatecol from mytable
.. it yells at me, and I can't use it to insert or update my destination columns.
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
Any way to get this into a datetime without starting over?