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 '19 at 15:05
  • The error is: "Incorrect syntax near 'column4'. Expecting '(', or SELECT May 10 '19 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 '19 at 11:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.