SSRS optional drop down parameter passing dates

Question

I currently have a set of drop down parameters that pass dates to a query. There is a display value available to select for each Work Week.

Create Date Parameter:

Display in the drop down: 2016 01 (1/3/2016) P01-16 Q1-16

Value being passed to the SQL is WEEK_BEGIN_DATE: 1/3/2016

New Ship Date Parameter:

Display in the drop down: 2016 01 (12/27/2016) P12-15 Q4-15

Value being passed to the SQL is SHIP_WEEK_BEGIN_DATE: 12/27/2016

I would like to be able to make both of these optional.

I tried the following in the parameter value SQL to get the parameter to allow nulls but I got a data type error. I did start going down the cast as varchar() route but it was getting messy quick.

SELECT 'NULL' AS WEEK_BEGIN_DATE
UNION
SELECT d.WEEK_BEGIN_DATE FROM DATE d

Any suggestions are much appreciated. Let me know if I can provide any other helpful info.

Answer 1

this should work, you don't need the single quotes

SELECT NULL AS WEEK_BEGIN_DATE
UNION
SELECT d.WEEK_BEGIN_DATE FROM DATE d

to make parameters optional you could in where clause write something like

where (WEEK_BEGIN_DATE = @date or @date is null)