How to identify the oldest partition based on date
I am trying to find a way to drop all partitions from a table, where the data is older than 6 months. The script will run monthly. I don't want the script to just drop the oldest partition in case someone / thing runs the script accidentally and deletes data which is less than 6months old. I also want the script to be smart enough to pick up when there are more than 6 months worth of partitions and drop all partitions older than 6 months. I have numerous tables partitioned by date, so was looking for a more scientific process than
0