last day of the month by passing month value

i had a column which contains month number . Based on that column i need to update a column with the last day of that month
LAST_DAY(TO_DATE(MONTH, 'MM')) this is how it is in oracle .
HOW TO DO IT IN VERTICA

Comments

  • Abhishek_RanaAbhishek_Rana Vertica Employee Employee
    LAST_DAY in vertica works on TIMESTAMP column as below:

    SELECT LAST_DAY('2008-02-28 23:30 PST') "Last";
        Last    
    ------------
     2008-02-29
    So in order to get exact results from last_dat function - you should use it on full time stamp. Giving it a try only with month number will give you ambiguous results.
  • Navin_CNavin_C Vertica Customer
    Hello Harish,

    Can you tell us how are you deriving the month number.

    In Vertica you can get the last day of any month by providing the argument as the date.
    nnani=> select * from test;
     month_number | month_date
    --------------+------------
                2 | 2013-02-10
                3 | 2013-03-15
                1 | 2013-01-04
    (3 rows)

    Now in order to get the last day of every month mentioned in the date column, we can do something like this using the Last_day function

    nnani=> SELECT LAST_DAY(month_date) from test;
      LAST_DAY
    ------------
     2013-02-28
     2013-03-31
     2013-01-31
    (3 rows)

    Hope this helps.

  • daniel=> select * from harish ;
     id |    date    | month
    ----+------------+-------
      1 | 2013-05-01 |     5
      2 | 2013-06-23 |     6
      3 | 2013-07-12 |     7
      4 | 2013-08-04 |     8
      5 | 2013-09-10 |     9
      6 | 2013-10-05 |    10
    (6 rows)

    daniel=> select id, date, month(date), last_day(('2013 ' || to_char(month, '09') || ' 01')::TIMESTAMP) from harish ;
     id |    date    | month |  last_day 
    ----+------------+-------+------------
      1 | 2013-05-01 |     5 | 2013-05-31
      2 | 2013-06-23 |     6 | 2013-06-30
      3 | 2013-07-12 |     7 | 2013-07-31
      4 | 2013-08-04 |     8 | 2013-08-31
      5 | 2013-09-10 |     9 | 2013-09-30
      6 | 2013-10-05 |    10 | 2013-10-31
    (6 rows)





Leave a Comment

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