I intend to create 2 partitions called New and Old of a table. An automatic job will check if a record is older than 90 days from current time (based on a field), it will move the record from New to Old.
The problem is that I cannot control which partition a select statement with range criteria will check. (One way to perform that is to check on application layer but I think it is not good idea)