Hi all!
I’ve created a lot of lobbies on the system but recently more users want to be able to filter their data by month, i.e. either type in April or 04 to filter down lobby results for anything that matched this month.
Tricky bit is that they simply want to use calendar month. So extracting a month from a date is not ideal. I tried toying with build in calendar_api options but didn’t get far yet.
Has anyone ever tried this before?
Best answer by Sajith D
Hi,
You can probably achieve this by matching the input parameter (lets say “MM”) against your data source and putting in a internal filter in the source query to look up only the data from this year. This is assuming that your issue stems from the fact that if you only use the Month, the query will return data for the same month for all years.
Simple example is as below
select col1, col3,etc…
from <your_view>
where to_char(<your_date_column>,'MM') = ‘$MM’ -- this is your input parameter
and to_char(<your_date_column>,'YYYY')= EXTRACT(YEAR FROM sysdate) -- to get only the curr year
Cheers.
+9What do you mean that users want to use calendar month but extracting month from date is not ideal?
so they want to select e.g. April but not a date range (from and to) but also not “writing” April either?
Using to_char(date, ‘Month’) would have been first thought but not sure why that is not ideal.
Hi,
You can probably achieve this by matching the input parameter (lets say “MM”) against your data source and putting in a internal filter in the source query to look up only the data from this year. This is assuming that your issue stems from the fact that if you only use the Month, the query will return data for the same month for all years.
Simple example is as below
select col1, col3,etc…
from <your_view>
where to_char(<your_date_column>,'MM') = ‘$MM’ -- this is your input parameter
and to_char(<your_date_column>,'YYYY')= EXTRACT(YEAR FROM sysdate) -- to get only the curr year
Cheers.
Thanks Sajith!
Think this is the best answer / solution ! :)
Thanks!
Kasia
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
