how to cast datetime2 as datetime

You are casting to timestamp in your code. Change to datetime.

SELECT CAST(offer_start_date AS datetime) FROM [ODS].[macaclient_offers]

Your sample select statement is trying to cast offer_start_date to timestamp not datetime.

If you do want a timestamp value from your datetime2 column you could the DatePart function to retrieve parts of the date and build it up yourself.

For example:

declare @date datetime2
set @date = GETUTCDATE()

select @date,
       DATEPART(hour, @date),
       DATEPART(minute, @date),
       DATEPART(second, @date)

MSDN reference to DatePart function.

Not sure why you're getting that error, I've not had the same issue. Example below works fine in my 2008 Management Studio.

create table #temp
(
    OrderId int,
    OrderDate datetime2
)

insert into #temp
(OrderId, OrderDate)
values
(1, GetUTCDate())

select *, CAST(OrderDate as datetime)
from #temp

drop table #temp