Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

decrypt to date datatype Expand / Collapse
Author
Message
Posted Thursday, April 3, 2014 7:40 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 10:33 AM
Points: 233, Visits: 319
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?
Post #1557972
Posted Tuesday, April 8, 2014 11:30 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 10:33 AM
Points: 233, Visits: 319
closing this out and trying msdn
Post #1559604
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse