Getting the Year and Month from an int date for a partition

John_Crossen

I have a field defined as int that stores a date value ... 20150101 (for January 1, 2015) for example.  The table has no datetime field.

I would like to partition the table based on YYYYMM  (201501, 201502, etc)

Suggestions on how to do that beyond adding a new column?

Thanks

John_Crossen

I'll answer my own question ... this worked
PARTITION BY left(cast (ColumnName as char(8)),6) 

id10t

and you will get partitioning by VARCHAR, not a big deal
But... why do we forget math so easily?

daniel=> select 20150101 // 100;
 ?column? 
----------
   201501
(1 row)

Now compare to your solution: {CAST AS CHAR}, {LEFT}

Regards.

John_Crossen

That is a good point and a better solution.  Thanks