Skip to main content

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

  • February 13, 2015
  • 3 replies
  • 24 views
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

    3 replies

    John_Crossen
    • John_Crossen
    • Author
    • New Participant
    • February 13, 2015
    I'll answer my own question ... this worked
    PARTITION BY left(cast (ColumnName as char(8)),6) 
    id10t
    Forum|alt.badge.img
    • id10t
    • Participating Frequently
    • February 13, 2015
    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
    • John_Crossen
    • Author
    • New Participant
    • February 13, 2015
    That is a good point and a better solution.  Thanks

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement