Please i need help with sql server data type conversion.
So i inserted this date '2022-12-01 after encrypting into a varbinary column DateAllowed.
INSERT INTO Tablename (Username, Password, UserType, DateAllowed)
VALUES ('Username', EncryptByKey(KEY_GUID('SymmetricKey'), 'Password'), EncryptByKey(KEY_GUID('SymmetricKey'), 'exampletext'), EncryptByKey(KEY_GUID('SymmetricKey'), '2022-12-01'));
It inserted successfully but when i select DateAllowed by decrypting and try to convert the decrypted text to date i get this error.
Msg 241, Level 16, State 1, Line 4
Conversion failed when converting date and/or time from character string.
The Query i used is below
OPEN SYMMETRIC KEY RMSSQLSymmetricKey DECRYPTION BY PASSWORD = 'Password';
SELECT CONVERT(date, DecryptByKey(DateAllowed)) AS 'DateAllowed1' FROM Tablename;
The interesting part is, if i use varchar i get the date back
SELECT CONVERT(varchar, DecryptByKey(DateAllowed)) AS 'DateAllowed1' FROM Tablename;
The problem with this too is that when i get the date as varchar C# program i make in visual studio does not recognise as date datatype even if i try to convert it to DateTime.
Please i need this solved urgently.
How can i convert the Date that is in varbinary back to date.