Framework Manager Date Range

[ntaulbee]

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!

[MFGF]

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.

[ntaulbee]

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?

[bdbits]

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.  ;)

[ntaulbee]

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)