I have been trying the derived table route, but have hit a very strange problem - I have 50 records in my table with a Type of D and a date value.

If I do this:

select * from (

select key_value, CONVERT(DATETIME2, key_value) AS key_date

from parameter_values WHERE data_type = 'D') PV

where convert(DATETIME,pv.key_date) between getdate() and dateadd(d, 10,getdate())

It still fails.

If I do this (remember, there are 50 records meeting the data_type criterion:

select * from (

select TOP 100 key_value, CONVERT(DATETIME2, key_value) AS key_date

from parameter_values WHERE data_type = 'D') PV

where convert(DATETIME,pv.key_date) between getdate() and dateadd(d, 10,getdate())

it works!

Changing to this:

select * from (

select TOP 100 PERCENT key_value, CONVERT(DATETIME2, key_value) AS key_date

from parameter_values WHERE data_type = 'D') PV

where convert(DATETIME,pv.key_date) between getdate() and dateadd(d, 10,getdate())

fails, but the one below works!

select * from (

select TOP 99 PERCENT key_value, CONVERT(DATETIME2, key_value) AS key_date

from parameter_values WHERE data_type = 'D') PV

where convert(DATETIME,pv.key_date) between getdate() and dateadd(d, 10,getdate())

I am tearing my hair out!