To perform some clever DML involving SWAP_PARTITIONS_BETWEEN_TABLES(), I'd like to be able to filter out all rows with a given partition key from a partitioned table, starting from an input date.
With PARTITION BY YEAR(sls_date) * 100 + MONTH(sls_date), and the partition affected by the date of today, it's easy:WHERE sls_date BETWEEN TRUNC(CURRENT_DATE,'MONTH')::DATE AND ADD_MONTHS(TRUNC(CURRENT_DATE,'MONTH'),1)-1.
But what do I do with PARTITION BY YEAR(sls_date) * 100 + WEEK(sls_date)?
Or other fancy partitioning expressions based on the element of time?
Is there some way of saying:WHERE partition_key = YEAR(sls_date) * 100 + MONTH(sls_date) to get all rows in the same partition as the date I supply?