6 Replies Latest reply: Dec 4, 2012 2:52 AM by 941719 RSS

    SYSDATE in DDMMYYYY inside single quotes

    user8941550
      Hi,

      I have a requirement like:

      Create Table A
      ( a number,
      CreationDate DATE)
      PARTITION BY RANGE (CreationDate)
      (
      Partition p_03122012 VALUES LESS THAN (TIMESTAMP' 2012-12-04 00:00:00'),
      Partition p_04122012 VALUES LESS THAN (TIMESTAMP' 2012-12-05 00:00:00'),
      Partition p_05122012 VALUES LESS THAN (TIMESTAMP' 2012-12-06 00:00:00'),
      Partition p_06122012 VALUES LESS THAN (TIMESTAMP' 2012-12-07 00:00:00')
      )

      NOTE: Partition are named as p_ddmmyyyy where ddmmyyyy is date.

      select * from dba_tab_partitions where table_name = 'A'

      Now my requirement is :

      select * from dba_tab_partitions where table_name = 'A' and partition_name > 'p_ddmmyyyy' (Here I want ddmmyyyy to be from sysdate i.e. date we get from - Select to_char(sysdate,'ddmmyyyy') from dual
      i.e. for today it becomes
      select * from dba_tab_partitions where table_name = 'A' and partition_name > 'P_04122012'
      So it returns me two rows.

      How can I do it in single quotes.

      Any suggestions please..
        • 1. Re: SYSDATE in DDMMYYYY inside single quotes
          Purvesh K
          Unsure what you meant by Single Quotes. But this is a simple way to achieve things:
          with data as
          (
            select 1 col, 'p_04122012' col2 from dual union all
            select 1 col, 'p_03122012' col2 from dual union all
            select 1 col, 'p_05122012' col2 from dual union all
            select 1 col, 'p_06122012' col2 from dual
          )
          select *
            from data
           where col2 < 'p_' || to_char(sysdate, 'ddmmyyyy');
          
          COL                    COL2       
          ---------------------- ---------- 
          1                      p_03122012
          • 2. Re: SYSDATE in DDMMYYYY inside single quotes
            AlbertoFaenza
            Hi,

            Not sure if I understood your question.

            Is it this what you want?
            select 'P_'||to_char(sysdate,'ddmmyyyy') part_name from dual;
            
            PART_NAME 
            ----------
            P_04122012
            Regards.
            Al
            • 3. Re: SYSDATE in DDMMYYYY inside single quotes
              Saubhik
              Your logic may not give the expected result, For example p_06122012 > p_01012013 and will not give you desired result. Instead use PARTITION_POSITION and HIGH_VALUE to get the desired partition name.
              • 4. Re: SYSDATE in DDMMYYYY inside single quotes
                AlbertoFaenza
                Hi,

                I think I get now what you want.

                Something like this:
                with data as
                (
                  select 1 col, 'p_04122012' part_name from dual union all
                  select 1 col, 'p_03122012' part_name from dual union all
                  select 1 col, 'p_05122012' part_name from dual union all
                  select 1 col, 'p_06122012' part_name from dual
                )
                select *
                  from data
                 where to_date(substr(part_name, 3), 'ddmmyyyy') > sysdate;
                
                       COL PART_NAME 
                ---------- ----------
                         1 p_05122012
                         1 p_06122012
                To see all partitions having name with date > sysdate.

                Regards.
                Al
                • 5. Re: SYSDATE in DDMMYYYY inside single quotes
                  941719
                  Is this what you want?

                  select *
                  from dba_tab_partitions
                  where table_name = 'A' and partition_name > 'P_'||to_char(sysdate, 'ddmmyyyy');
                  • 6. Re: SYSDATE in DDMMYYYY inside single quotes
                    user8941550
                    Thanks Guys for all the help. Actually I should have mentioned, A is just a sample table. I have hundreds of partitions in real.
                    I have achieved the result using following query:

                    select * from user_tab_partitions where table_name = 'A' and partition_position > (Select partition_position from user_tab_partitions where table_name = 'A' and partition_name = 'P_'||to_char(sysdate,'ddmmyyyy'))

                    Any way to write it in a better manner? Or is it fine.

                    Thanks a lot..