0
    OPEN SYMMETRIC KEY TblUserDefinedFieldKey DECRYPTION BY CERTIFICATE mDNACert
SELECT * FROM
(
  SELECT u.public_id, u.instance_id, u.state, u.tag, udf.name AS [user_defined_key], 
  CONVERT(NVARCHAR(MAX), 
  DECRYPTBYKEY(value_enc)) AS [user_defined_value]
  FROM tbl_user_defined_field udf
  join tbl_user u on u.user_id = udf.user_id
  join tbl_solution_revision sr on sr.solution_revision_id = u.solution_revision_id
  join tbl_solution s on s.solution_id = sr.solution_id
  WHERE 
  s.name = 'myName'
  and u.group_id = '1234' 
  --and u.state = 'Active'
) as sourcetbl
PIVOT
(
    max (user_defined_value) for user_defined_key in 
                           (column1, column2, column3, column4)
) as pvt
CLOSE SYMMETRIC KEY TblUserDefinedFieldKey

Given the above query, and column4 being a date field I am trying to cast the value of that field to a readable date (the value is returned as 1543507200000).

But doing this: max (user_defined_value) for user_defined_key in (column1, column2, column3, cast(column4 as date) as column4) gives a syntax error!

3
  • 3
    It may help you get an answer faster if you post the syntax error. May 3, 2019 at 15:05
  • The error is: "Incorrect syntax near 'column4'. Expecting '(', or SELECT May 10, 2019 at 8:54
  • The complication is that user_defined_value is actually a nullable nvarchar and it's value is obviously just a string. But, IF the corresponding user_defined_key column value for that row is 'date', then I want to cast the value therein (which I assume is some value in minutes since a certain date (1970 or something ?) to a proper formatted date. (in UK English :) ) May 10, 2019 at 11:17

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.