Deriving month year from column and use that in range partition

Question

Combining 2 questions here as part of my requirement.

I want to do monthly range partition on date column and date column will store the value in Year-month format

1) Is there any function to get date in this format

for eg: i/p - 04-07-2015 o/p - 2015-07

I have acheived using this below way but in this way data will be stored in String Format..

My 2nd Question is, I will be loading data for aug in sep and will have 1 month of data in each partition based on the column which will be derived above...

SELECT TO_CHAR ( (TRUNC (TO_DATE (range_st_date), 'YYYY')),
'YYYY') || '-' || RTRIM (TO_CHAR ( (TRUNC (TO_DATE (range_st_date),  
'MM')), 'MM')) FROM DUAL;

Thanks for help.

select to_char(to_date(range_start_dt, 'DD-MM-YYYY'),'MM-YYYY') from table ; this way also we can get it . but it will give in string format..Any way we can get it in date format.. — aadi, Aug 28 '15 at 09:44
If all you're trying to do is partition a table for each month, you don't need to change the data in the date column at all. The Oracle range partitioning will take care of that for you (eg. it knows that a date of 4th July 2015 will go in the partition with values less than 1st August 2015. — Boneist, Aug 28 '15 at 10:04
Actually , The column stores the calendar year month in yy month format.. I have to do range partition based on that column..Since YYYY-MM format is achievable but using to_char which will be of string data type and then i wont be able to derive partition dates from that column — aadi, Aug 28 '15 at 10:21
data in a DATE datatype column doesn't have any format, apart from the internal Oracle DATE datatype. — Boneist, Aug 28 '15 at 10:23

score 0 · Answer 1 · answered Aug 28 '15 at 10:29

If all you're trying to do is set up range partitioning by month on your table, you don't need to do anything to the date for it to go in the right partition. E.g.:

create table test1 (col1 number,
                    col2 date)
partition by range (col2)
 (partition part_201507 values less than (to_date('01/08/2015', 'dd/mm/yyyy')),
  partition part_201508 values less than (to_date('01/09/2015', 'dd/mm/yyyy')),
  partition max_val values less than (maxvalue));

insert into test1
select 1, to_date('14/07/2015 01:46:25', 'dd/mm/yyyy hh24:mi:ss') from dual union all
select 2, to_date('08/08/2015 14:19:34', 'dd/mm/yyyy hh24:mi:ss') from dual;

commit;

select * from test1 partition (part_201507);

      COL1 COL2                 
---------- ---------------------
         1 14/07/2015 01:46:25  

select * from test1 partition (part_201508);

      COL1 COL2                 
---------- ---------------------
         2 08/08/2015 14:19:34  

alter session set nls_date_format = 'dd/mm/yyyy hh24:mi:ss';

If you're after the month + year so you can, for example, do an aggregate query grouped by month, then simply truncate the date to a month, e.g

select col1,
       col2,
       trunc(col2, 'mm') mon_col2
from   test1;

      COL1 COL2                  MON_COL2             
---------- --------------------- ---------------------
         1 14/07/2015 01:46:25   01/07/2015 00:00:00  
         2 08/08/2015 14:19:34   01/08/2015 00:00:00

Data stored as a DATE datatype doesn't have any particular format except for Oracle's own internal date format. So, if you want to output the date in a human-readable format, it must be converted into a string for display purposes. This can be implicit, based on the format you have set in the nls_date_format parameter, or it can be explicit using TO_CHAR() with whatever format you wish it to be displayed as.

In short, if you want your date to continue to be stored as a DATE datatype, then don't worry about its format, worry only about the precision level you wish to store the date as (seconds/minutes/hours/day/month/year).

Deriving month year from column and use that in range partition

1 Answers1