filter a column based on a 2 Dates selected in the date prompts

[sandeep8000861]

Hi,

I have a set two date prompts (I call them [Start Date] and [End Date]) on a date column [Closed Date]. These dates are in Prompt page1. There is a value prompt on another column [JourneyDirection]. This is in Prompt page2.
I am using SQL server 2005 as DWH. The [Closed Date] has date in dd/mm/yyyy hh:mm:ss AM/PM format.

So now, I have to display only those [JourneyDirection]s in the [JourneyDirection] prompt that are between the [Start Date] and [End Date].

I tried changing the format of the string picked up in both the date prompt using cast_varchar and month functions. In whatever format i put the [Start Date] and [End Date], they are not filtering the [Closed Date]. Obviously, there is a mismatch in the date format.

Can someone help me to
a) getrid of the time part in the [Closed Date]
b) then format my [Start Date] and [End Date] in such a way that matches the [Closed Date] and the filter works.

Please help me with this issue.

[sandeep8000861]

Hi All,

I got this issue of mine fixed by myself.

I created two QueryItems to hold the parameters for startdate (param_StartDate) and end date (param_EndDate). Used two new Data items to convert these prompt-selcted date ie., params into date-time.
The expressions that worked for me is:
DATEADD({dd}, 0, DATEDIFF({dd}, 0, [StartDate]))
DATEADD({dd}, 0, DATEDIFF({dd}, 0, [EndDate]))

So the 4 items are as below:
StartDate has ?param_StartDate? in it.
EndDate has ?param_EndDate? in it.
Start_Date has DATEADD({dd}, 0, DATEDIFF({dd}, 0, [StartDate])) in it.
End_Date has DATEADD({dd}, 0, DATEDIFF({dd}, 0, [EndDate])) in it.

The filter is [CloseDate] between [Start Date] and [End Date]

This worked just fine.
Forget not to add any other necessary filters to this prompt quey as well before testing the count of prompt values being displayed in the prompt.

[NaviGator]

Thank you for sharing ..