We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


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

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?

Thanks

Comments

  • 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;
    ?column?
    ----------
    201501
    (1 row)
    Now compare to your solution: {CAST AS CHAR}, {LEFT}

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

Leave a Comment

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