last day of the month by passing month value

harish_babu_yar

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

Abhishek_Rana

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_C

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_Leybovic

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)