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

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?



  • I'll answer my own question ... this worked
    PARTITION BY left(cast (ColumnName as char(8)),6) 
  • and you will get partitioning by VARCHAR, not a big deal ;)
    But... why do we forget math so easily?
    daniel=> select 20150101 // 100;
    (1 row)
    Now compare to your solution: {CAST AS CHAR}, {LEFT}

  • That is a good point and a better solution.  Thanks

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file