I have two dates (start and end date). I want to create a filter in framework manager on my table so that when the user selects any field from that table in report studio; they are prompted to select a date and then the query would pull back records where that date fell in the range of the start and end date.
Example:
Record # Start Date End Date
1 1/1/15 7/1/15
2 7/2/15 12/31/15
3 1/1/16 12/31/99
If the user was prompted at runtime and put 9/6/15, then only record 2 would come back.
Any ideas on the syntax of the filter I am creating? Thanks!
Quote from: ntaulbee on 29 Jan 2016 02:31:58 PM
I have two dates (start and end date). I want to create a filter in framework manager on my table so that when the user selects any field from that table in report studio; they are prompted to select a date and then the query would pull back records where that date fell in the range of the start and end date.
Example:
Record # Start Date End Date
1 1/1/15 7/1/15
2 7/2/15 12/31/15
3 1/1/16 12/31/99
If the user was prompted at runtime and put 9/6/15, then only record 2 would come back.
Any ideas on the syntax of the filter I am creating? Thanks!
How about
[Start Date] <= ?YourDateParameter? and [End Date] >= ?YourDateParameter?
MF.
Thanks, I believe that would work if not for my date/time format on the date fields.
Here is the filter I put on the table:
[A_VERSION_START_DTTM] >= ?SelectDate? and [A_VERSION_END_DTTM] <= ?SelectDate?
Here is the SQL being generated for the prompt
WHERE
T0.A_VERSION_START_DTTM >= :SelectDate: AND
T0.A_VERSION_END_DTTM <= :SelectDate:
However, when I put in a date in the prompt box I get returned 'No Data Available' even though there are records.
Any ideas?
It looks like the expression you entered might be backwards. The generated SQL is asking for a start date on or later than the chosen date and end date on or less than the chosen date. Which is unlikely to ever be the case. Swap your >= and <= operators and I think you will be fine.
Unless of course you have figured out time travel. ;)
Quote from: bdbits on 03 Feb 2016 11:54:30 AM
It looks like the expression you entered might be backwards. The generated SQL is asking for a start date on or later than the chosen date and end date on or less than the chosen date. Which is unlikely to ever be the case. Swap your >= and <= operators and I think you will be fine.
Unless of course you have figured out time travel. ;)
Thank you!!!!!!! Its working now! (no time travel required)