Trying to filter msdb..sysjobs by job_id I'm attempting to determine the name of the SQL Server Agent Job that is currently running by comparing the output of master.sys.dm_exec_sessions.program_name to msdb..sysjobs.job_id 

I'm using the following T-SQL to try to diagnose why I cannot get the results I want:

SELECT program_name, '|' + SUBSTRING(program_name,30,34) + '|' AS JobIDSubstring, t.text
FROM sys.dm_exec_sessions s 
    INNER JOIN sys.dm_exec_requests r on s.session_id = r.session_id
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
--  INNER JOIN msdb..sysjobsteps sj on SUBSTRING(program_name,30,34) = cast(cast(sj.job_id as varbinary(max)) as varchar(max))
WHERE 
    program_name like 'SQLAgent%' ;

SELECT CAST(job_id as varbinary(max)) as JobStepID, CAST(step_id as varbinary(max)) as StepID, *
FROM msdb..sysjobsteps;

SELECT program_name, '|' + SUBSTRING(program_name,30,34) + '|' AS JobIDSubstring, t.text
FROM sys.dm_exec_sessions s 
    INNER JOIN sys.dm_exec_requests r on s.session_id = r.session_id
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
    INNER JOIN msdb..sysjobsteps sj on SUBSTRING(program_name,30,34) = cast(cast(sj.job_id as varbinary(max)) as varchar(max))
WHERE 
    program_name like 'SQLAgent%' ;


Results:



The last query should return results, however it returns zero rows since the JOIN clause is clearly not working.  How should I construct this so the JOIN from sys.dm_exec_sessions to msdb..sysjobsteps works?
Stop using CAST and use CONVERT. Specifically because, in this case, you need to use a certain style parameter in order to get the right string comparison (otherwise it is just converting 0xwhatever to the string value represented by 0xwhatever, which is not 0xwhatever. Compare:

SELECT 
  CONVERT(VARCHAR(32), 0x48656C6C6F21),
  CONVERT(VARCHAR(32), 0x48656C6C6F21, 1);
  ----- only difference -------------^^^


Results:

------   --------------
Hello!   0x48656C6C6F21


Hello! is actually the string represented by the binary value. The second column is just the binary value displayed as a string. Big difference!

With that in mind, change the join criteria on your last query to:

INNER JOIN msdb.dbo.sysjobsteps sj -- why leave out dbo?
  ON SUBSTRING(s.[program_name],30,34) = -- prefix your columns
  CONVERT(VARCHAR(34), CONVERT(VARBINARY(32), sj.job_id), 1) -- no need for MAX types here
  ------- this is the important piece ------------------^^^