I'm also not familiar with SQL Server, but it looks like you're converting the data value into a substr of a hash in the query. I'd do that outside the query using PHP, perhaps something like:
$partyid = '123987';
$hashedpartyid = substr(md5($partyid), 3, 32);
$user_details = $DB->get_record_sql("
SELECT * FROM mdl_user WHERE id = (SELECT TOP 1 userid FROM mdl_user_info_data
JOIN mdl_user_info_field ON mdl_user_info_field.id=mdl_user_info_data.fieldid WHERE data = :hashedpartyid AND shortname = 'partyid')", ["hashedpartyid" => $hashedpartyid]);