last day of the month by passing month value

i had an integer column in one table from which i need to populate the last date of that month into another table
LAST_DAY(TO_DATE(MONTH, 'MM'))
this is how it is right now in oracle 
so is there any way to achieve this.

Comments

  • 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
  • Navin_CNavin_C Vertica Customer
    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
  • Navin_CNavin_C Vertica Customer
    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
  • 8,1,1,1,0,411,xxx
    8,1,1,2,1,358,xxx
    8,1,1,2,2,358,xxx
    8,1,1,3,1,318,xxx
    8,1,1,3,2,318,xxx
    8,1,1,3,19,318,xxx
    8,1,1,3,20,318,xxx
    8,1,1,3,28,318,xxx

    the 8 is the month column


      MONTH INTEGER,  BRANCH INTEGER,  DEPT INTEGER,  CLASS INTEGER,  SUB_CLASS INTEGER,  SSR NUMERIC(8,2),  ENV_CODE  VARCHAR(1000)
    table structure
  • 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)



  • Navin_CNavin_C Vertica Customer
    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)


  • Navin_CNavin_C Vertica Customer
    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.

Leave a Comment

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