Ну вот так можно сделать "в лоб"
select last_name
, start_date
, CASE
WHEN DATENAME(DW, DATEADD(day, 0, DATEADD(month, 6, start_date))) = 'Friday' then DATEADD(day, 0, DATEADD(month, 6, start_date))
WHEN DATENAME(DW, DATEADD(day, 1, DATEADD(month, 6, start_date))) = 'Friday' then DATEADD(day, 1, DATEADD(month, 6, start_date))
WHEN DATENAME(DW, DATEADD(day, 2, DATEADD(month, 6, start_date))) = 'Friday' then DATEADD(day, 2, DATEADD(month, 6, start_date))
WHEN DATENAME(DW, DATEADD(day, 3, DATEADD(month, 6, start_date))) = 'Friday' then DATEADD(day, 3, DATEADD(month, 6, start_date))
WHEN DATENAME(DW, DATEADD(day, 4, DATEADD(month, 6, start_date))) = 'Friday' then DATEADD(day, 4, DATEADD(month, 6, start_date))
WHEN DATENAME(DW, DATEADD(day, 5, DATEADD(month, 6, start_date))) = 'Friday' then DATEADD(day, 5, DATEADD(month, 6, start_date))
WHEN DATENAME(DW, DATEADD(day, 6, DATEADD(month, 6, start_date))) = 'Friday' then DATEADD(day, 6, DATEADD(month, 6, start_date))
ELSE null end AS next_salary_revision
from s_emp
Можно и проще, приду с обеда, отпишу, если оч. надо
пы сы
ну а с форматом вывода - смотреть надо, convert(char, дата, формат)