Query to get the last day of each month in a year.

Database
Enthusiast

Query to get the last day of each month in a year.

hi,

I have a empl table. i want to get a report for all those employees who joined on the last day of each month. Let's say for year 2005
all employees who joined on 31st jan, 28 th feb, 31 march----- 31st dec
Also i dont wana use any sort of hardcoding. Any help in this regard will be appreciated.

Thanks
Angel
6 REPLIES
Enthusiast

Re: Query to get the last day of each month in a year.

Hi,
what about joining your empl table with sys_calendar.calendar on employee_date = the last day of every month? Obviously you have to extract from sys_calendar.calendar view the last day for each month, but it isn't difficult at all.

Hope this helps,
Bye
TDUser
Senior Apprentice

Re: Query to get the last day of each month in a year.

Hi Angel,

where
hire_date = add_months(hire_date,1) - extract(day from add_months(hire_date,1))

Dieter
Enthusiast

Re: Query to get the last day of each month in a year.

Hi,

If possible can you give me the exact query.

Thanks
Enthusiast

Re: Query to get the last day of each month in a year.

Thanks Dieter
it worked well

Re: Query to get the last day of each month in a year.

SEL ADD_MONTHS(CURRENT_DATE-EXTRACT(DAY FROM CURRENT_DATE),1);

or

SEL ADD_MONTHS((CURRENT_DATE-EXTRACT(DAY FROM CURRENT_DATE)+1),1)-1;

Re: Query to get the last day of each month in a year.

The first query above does not return correct results when the previous month is shorter than the current month.

For example, April has 30 days and May has 31.  If you run it in May, the first query returns the 30th of May instead of the 31st.

The second query above is good.

SELECT

CAST('2014-05-15' AS DATE) AS The_Date,

ADD_MONTHS(The_Date-EXTRACT(DAY FROM The_Date),1) AS EOM_v1,

ADD_MONTHS((The_Date-EXTRACT(DAY FROM The_Date)+1),1)-1 AS EOM_v2,

ADD_MONTHS(The_Date, 1) - EXTRACT(DAY FROM ADD_MONTHS(The_Date, 1)) AS EOM_v3

;

Returns:

The_Date: 2014-05-15

EOM_v1: 2014-05-30

EOM_v2: 2014-05-31

EOM_v3: 2014-05-31

Version 2 and 3 work correctly.  Don't use Version 1.

I like Version 2, it's more succinct.  Version 3 also works and is arguably easier to understand at first glance.