Switching date format in Crystal Reports

Question

I have been trying unsuccessfully to find a way to filter out records from a report. I have a field titled time_period in a view that returns a date in the "MM/YYYY" format. I have two parameters titled startMonth and endMonth which are in "MM/DD/YYYY".

Somehow I need to be able to make sure that the date_grouping field value is between the two parameters.

Here's what I have so far...

 {location_total_kpi_view.time_period} >= 
 Date(Month({?startMonth}) + Year({?startMonth})) and
 {location_total_kpi_view.time_period} <= 
 Date(Month({?endMonth}) + Year({?endMonth}))

It excludes all records. Any suggestions?

Answer 1

Try converting them both to yyyy/MM format and then into dates to make sure that the comparisons are equal, like this:

Date(ToText({location_total_kpi_view.time_period}, "yyyy/MM")) >= 
Date(ToText({?startMonth}, "yyyy/MM")) and
Date(ToText({location_total_kpi_view.time_period}, "yyyy/MM")) <=  
Date(ToText({?endMonth}, "yyyy/MM")) and