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.
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.
Thank you for sharing ..