Надеюсь, это поможет,
select id,
NewsHeadline as news_headline,
NewsText as news_text,
state,
CreatedDate as created_on
from News
WHERE CreatedDate >= cast(dateadd(day, -7, GETDATE()) as date)
and CreatedDate < cast(GETDATE()+1 as date) order by CreatedDate desc