Date parameter issue when created from query

I have a date parameter for a report that is intended to search 'within the last X week/month...'. The SQL for this is basically WHERE [datefield] >= @.dateparam AND [datefield] <= GETDATE().

The @.dateparam is defined in the report as 'From query', the value field is 'thedate', and the label field is 'thetext', and the dataset is:

Date Query dataset

SELECT GETDATE()-7 as thedate, '1 Week' as thetext, 1 as theorder
UNION
SELECT GETDATE()-14 as thedate,'2 Weeks' as thetext, 2 as theorder
UNION
SELECT GETDATE()-21 as thedate,'3 Weeks' as thetext, 3 as theorder
UNION
SELECT GETDATE()-31 as thedate,'1 Month' as thetext, 4 as theorder
UNION
SELECT GETDATE()-62 as thedate,'2 Months' as thetext, 5 as theorder
UNION
SELECT GETDATE()-(31*3) as thedate,'3 Months' as thetext, 6 as theorder
UNION
SELECT GETDATE()-(31*6) as thedate,'6 Months' as thetext, 7 as theorder
UNION
SELECT GETDATE()-365 as thedate,'1 Year' as thetext, 8 as theorder
ORDER BY 3

When I go to run the report and selecting a value for the date parameter, there is never any output, and the data selection combo box just gets reset to show <select a value>.

What am I doing wrong?

What happens if you set your "From query" up with converted dates as strings, versus a datetime type, and then re-cast the value in your WHERE clause? I'm thinking maybe this is a date formatting problem....

>L<

|||Thank you!

I quickly changed the dataset to be:

Code Snippet

SELECT CONVERT(nvarchar(10),GETDATE()-7, 101) as thedate, '1 Week' as thetext, 1 as theorder
...

...

ORDER BY 3

And the query worked.