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.
LAST_DAY(TO_DATE(MONTH, 'MM'))
this is how it is right now in oracle
so is there any way to achieve this.
0
Comments
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
and i tried this in vertica select LAST_DAY(TO_DATE(10::varchar, 'MM'))
this is giving for the yr 0001 bc
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.
This will better helps us analyzing your requirement and projecting a solution accordingly.
Thanks
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
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)
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)
For your scenario
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.
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)
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.