last day of the month by passing month value

Hi,

We do have same functions in the vertica database as well

I am curious to know if you execute the below in oracle, what would be output, what year it will assign, we are not passing any year here.

select last_day(TO_DATE('12', 'MM'));

Thanks

its getting the current yr
and i tried this in vertica select LAST_DAY(TO_DATE(10::varchar, 'MM')) 
this is giving for the yr 0001 bc

Hi Shobhit / Harish

The Last_day function always works on Date and timestamp values

If you try to do

nnani=> select TO_DATE('12', 'MM');
    TO_DATE
---------------
 0001-12-01 BC
(1 row)

Last day takes this as  the argument and produces the last day of 0001-12-01 month

nnani=> select last_day(TO_DATE('12', 'MM'));
   last_day
---------------
 0001-12-31 BC
(1 row)

If you can use a date column to derive the Last_day result then this should work fine.

nnani=> select TO_DATE('20131200', 'YYYYMMDD');
  TO_DATE
------------
 2013-12-01
(1 row)

then using the LAST_DAY function

nnani=> select last_day(TO_DATE('20131200', 'YYYYMMDD'));
  last_day
------------
 2013-12-31
(1 row)


#Harish
Can u show us how do u derive the month number, I mean what is the start date and end date or is it
from 1 - 12 always.

actual i am receiving the data in csv where the month is loaded into one table where it is an integer column.from that table i need to populate the last day of that month into another table column where it is a date

Can you show us sample data in CSV and the month loaded into one table where it is an int column from that table.

This will better helps us analyzing your requirement and projecting a solution accordingly.

Thanks

And why do you create many questions and don't follow to them?
https://community.vertica.com/vertica/topics/last_day_of_the_month_by_passing_month_value-1d94f2

daniel=> create table harish (month int, last_day date);
CREATE TABLE
daniel=> copy harish (month, last_day as last_day(('2013 ' || to_char(month, '09') || ' 01')::TIMESTAMP)) from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1
>> 2
>> 3
>> 4
>> 5
>> \.
daniel=> select * from harish ;
 month |  last_day 
-------+------------
     1 | 2013-01-31
     2 | 2013-02-28
     3 | 2013-03-31
     4 | 2013-04-30
     5 | 2013-05-31
(5 rows)

or more portable version: last_day(date(year(now()) || to_char(month, '09') || ' 01'))

daniel=> copy harish (month, last_day as last_day(date(year(now()) || to_char(month, '09') || ' 01'))) from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 9
>> 10
>> 11
>> 12
>> \.
daniel=> select * from harish ;
 month |  last_day 
-------+------------
     1 | 2013-01-31
     2 | 2013-02-28
     3 | 2013-03-31
     4 | 2013-04-30
     5 | 2013-05-31
     9 | 2013-09-30
    10 | 2013-10-31
    11 | 2013-11-30
    12 | 2013-12-31
(9 rows)

I liked the later one provided by Daniel.

For your scenario

actual i am receiving the data in csv where the month is loaded into one table where it is an integer

column.from that table i need to populate the last day of that month into another table column where it

is a date

Please refer to this

nnani=> select * from harish;
 MONTH | BRANCH | DEPT | CLASS | SUB_CLASS |  SSR   | ENV_CODE
-------+--------+------+-------+-----------+--------+----------
     8 |      1 |    1 |     3 |         2 | 318.00 | ddd
     5 |      1 |    5 |     3 |        28 | 318.00 | ccc
     8 |      1 |    1 |     3 |        28 | 318.00 | ccc
     8 |      1 |    1 |     1 |         0 | 411.00 | aaa
     8 |      1 |    1 |     1 |         0 | 411.00 | aaa
     8 |      1 |    1 |     2 |         1 | 358.00 | bbb
     7 |      1 |    5 |     3 |        28 | 318.00 | abc
    10 |      1 |    5 |     3 |        28 | 318.00 | abb



nnani=> select * from harish_new;
 MONTH | DERIVED_DATE | LAST_DATE_OF_MONTH
-------+--------------+--------------------
(0 rows)

Insert statement -

INSERT INTO harish_new
select
month,
CAST(CAST('2013'||TRIM(leading ' ' from TO_CHAR(month,'09'))||'01' AS VARCHAR) AS DATE),
LAST_DAY(CAST(CAST('2013'||TRIM(leading ' ' from TO_CHAR(month,'09'))||'01' AS VARCHAR) AS TIMESTAMP))
FROM harish

nnani=> select * from harish_new;
 MONTH | DERIVED_DATE | LAST_DATE_OF_MONTH
-------+--------------+--------------------
     8 | 2013-08-01   | 2013-08-31
     8 | 2013-08-01   | 2013-08-31
     8 | 2013-08-01   | 2013-08-31
     8 | 2013-08-01   | 2013-08-31
     8 | 2013-08-01   | 2013-08-31
    10 | 2013-10-01   | 2013-10-31
     5 | 2013-05-01   | 2013-05-31
     7 | 2013-07-01   | 2013-07-31
(8 rows)

Hope this helps.

there are a lot of options...

daniel=> select month, last_day(add_months('2013-01-01', month - 1)) from months;
 month |  last_day 
-------+------------
     1 | 2013-01-31
     2 | 2013-02-28
     3 | 2013-03-31
     4 | 2013-04-30
     5 | 2013-05-31
     6 | 2013-06-30
     9 | 2013-09-30
    10 | 2013-10-31
    12 | 2013-12-31
(9 rows)

Yes, there are lot of possibilities

select
month,
LAST_DAY(CAST((case when month = 1 then '2013-01-01'
     when month = 2 then '2013-02-01'
     when month = 3 then '2013-03-01'
     when month = 4 then '2013-04-01'
     when month = 5 then '2013-05-01'
     when month = 6 then '2013-06-01'
     when month = 7 then '2013-07-01'
     when month = 8 then '2013-08-01'
     when month = 9 then '2013-09-01'
     when month = 10 then '2013-10-01'
     when month = 11 then '2013-11-01'
     when month = 12 then '2013-12-01' END) AS DATE))
from harish ORDER BY month;

 month |  LAST_DAY
-------+------------
     1 | 2013-01-31
     2 | 2013-02-28
     3 | 2013-03-31
     4 | 2013-04-30
     5 | 2013-05-31
     6 | 2013-06-30
     7 | 2013-07-31
     8 | 2013-08-31
     9 | 2013-09-30
    10 | 2013-10-31
    11 | 2013-11-30
    12 | 2013-12-31
(12 rows)

Hope this helps.