Using your existing pipeline activites this is probably the simplest approach.

1. Do your lookup exactly how you are

2. In your Set Variable Activity use this dynamic content expression:

@formatDateTime(activity('Lookup1').output.firstRow.xdate, 'yyyy-MM-dd')

3. Your Script Activity can be simplified to just this:

delete from dbo.temp_paycor_payroll
where [Check Date] = '@{variables('xdate')}'

This should do it! Cheers!