dropping partitions using date function

[BeefStu]

I am trying to create a script that drops partitions, which are older than
"N' days.

My partitions names are always in this format "P_2010_10_20" (P_YYYY_MM_DD)


I want to do something like this, but the syntax is not correct:

select partition_name from user_tab_partitions where table_name = 'XXX'
and partition_name < (sysdate - Ndays)


Can somebody help me with the correct syntax.

Thanks to all who answer

[LKBrwn_DBA]

Try this:

SELECT partition_name
  FROM user_tab_partitions
 WHERE table_name = 'XXX'
       AND partition_name < 'P_' || TO_CHAR ( (SYSDATE - 30), 'YYYY_MM_DD');

[BeefStu]

LK, works like great. Thanks