Get Last date of every Month in a year

Advertisement
Hi All,
I need to find last date of month for given year.
Example:
I used to pass date or year such as 2012 or 01-01-2012(DD-MM-YYYY)
SQL query needs to return last date of every month such as
31-01-2012
28-02-2012
31-03-2012
30-04-2012
31-12-2012
for above requirement i have written the following SQL
select rownum as row_count,
case when rownum=1 then last_day(to_date(add_months(trunc(to_date('01-01-2014','DD-MM-YYYY'), 'YYYY'), level - 1), 'DD-MM-YY')) end as Jan_month,
case when rownum=2 then last_day(to_date(add_months(trunc(to_date('01-01-2014','DD-MM-YYYY'), 'YYYY'), level - 1), 'DD-MM-YY')) end as Feb_month,
case when rownum=3 then last_day(to_date(add_months(trunc(to_date('01-01-2014','DD-MM-YYYY'), 'YYYY'), level - 1), 'DD-MM-YY')) end as mar_month,
case when rownum=4 then last_day(to_date(add_months(trunc(to_date('01-01-2014','DD-MM-YYYY'), 'YYYY'), level - 1), 'DD-MM-YY')) end as apr_month,
case when rownum=5 then last_day(to_date(add_months(trunc(to_date('01-01-2014','DD-MM-YYYY'), 'YYYY'), level - 1), 'DD-MM-YY')) end as may_month,
case when rownum=6 then last_day(to_date(add_months(trunc(to_date('01-01-2014','DD-MM-YYYY'), 'YYYY'), level - 1), 'DD-MM-YY')) end as jun_month,
case when rownum=7 then last_day(to_date(add_months(trunc(to_date('01-01-2014','DD-MM-YYYY'), 'YYYY'), level - 1), 'DD-MM-YY')) end as jul_month,
case when rownum=8 then last_day(to_date(add_months(trunc(to_date('01-01-2014','DD-MM-YYYY'), 'YYYY'), level - 1), 'DD-MM-YY')) end as aug_month,
case when rownum=9 then last_day(to_date(add_months(trunc(to_date('01-01-2014','DD-MM-YYYY'), 'YYYY'), level - 1), 'DD-MM-YY')) end as sep_month,
case when rownum=10 then last_day(to_date(add_months(trunc(to_date('01-01-2014','DD-MM-YYYY'), 'YYYY'), level - 1), 'DD-MM-YY')) end as oct_month,
case when rownum=11 then last_day(to_date(add_months(trunc(to_date('01-01-2014','DD-MM-YYYY'), 'YYYY'), level - 1), 'DD-MM-YY')) end as nov_month,
case when rownum=12 then last_day(to_date(add_months(trunc(to_date('01-01-2012','DD-MM-YYYY'), 'YYYY'), level - 1), 'DD-MM-YY')) end as dec_month
from dual connect by level <= 12 order by rownum;
Result
Jan_Month
Feb_Month
Mar_Month
Apr_Month
may_month
jun_month
jul_month
aug_month
sep_month
oct_month
nov_month
dec_month
1
31-01-2014
2
28-02-2014
3
31-03-2014
4
30-04-2014
5
31-05-2014
6
30-06-2014
7
31-07-2014
8
31-08-2014
9
30-09-2014
10
31-10-2014
11
30-11-2014
12
31-12-2012
Excepted Result:
am excepted result as single row such as
Jan_Month
Feb_Month
Mar_Month
Apr_Month
may_month
jun_month
jul_month
aug_month
sep_month
oct_month
nov_month
dec_month
31-01-2012
28-02-2012
31-03-2012
30-04-2012
31-05-2012
30-06-2012
31-07-2012
31-08-2012
30-09-2012
31-10-2012
30-11-2012
31-12-2012
Kindly give me suggestion to archive above result.
Thanks&Regards
Sami
Advertisement

Replay

I agree with Marcus Pivot is the way to go about this... But on the other hand you almost solved it yourself.. just a max function was needed for your expected output:
select
max(case  when rownum=1 then last_day(to_date(add_months(trunc(to_date('01-01-2014','DD-MM-YYYY'), 'YYYY'), level - 1), 'DD-MM-YY')) end) as Jan_month,
max(case  when rownum=2 then last_day(to_date(add_months(trunc(to_date('01-01-2014','DD-MM-YYYY'), 'YYYY'), level - 1), 'DD-MM-YY')) end) as Feb_month,
max(case  when rownum=3 then last_day(to_date(add_months(trunc(to_date('01-01-2014','DD-MM-YYYY'), 'YYYY'), level - 1), 'DD-MM-YY')) end) as mar_month,
max(case  when rownum=4 then last_day(to_date(add_months(trunc(to_date('01-01-2014','DD-MM-YYYY'), 'YYYY'), level - 1), 'DD-MM-YY')) end) as apr_month,
max(case  when rownum=5 then last_day(to_date(add_months(trunc(to_date('01-01-2014','DD-MM-YYYY'), 'YYYY'), level - 1), 'DD-MM-YY')) end) as may_month,
max(case  when rownum=6 then last_day(to_date(add_months(trunc(to_date('01-01-2014','DD-MM-YYYY'), 'YYYY'), level - 1), 'DD-MM-YY')) end) as jun_month,
max(case  when rownum=7 then last_day(to_date(add_months(trunc(to_date('01-01-2014','DD-MM-YYYY'), 'YYYY'), level - 1), 'DD-MM-YY')) end) as jul_month,
max(case  when rownum=8 then last_day(to_date(add_months(trunc(to_date('01-01-2014','DD-MM-YYYY'), 'YYYY'), level - 1), 'DD-MM-YY')) end) as aug_month,
max(case  when rownum=9 then last_day(to_date(add_months(trunc(to_date('01-01-2014','DD-MM-YYYY'), 'YYYY'), level - 1), 'DD-MM-YY')) end) as sep_month,
max(case  when rownum=10 then last_day(to_date(add_months(trunc(to_date('01-01-2014','DD-MM-YYYY'), 'YYYY'), level - 1), 'DD-MM-YY')) end) as oct_month,
max(case  when rownum=11 then last_day(to_date(add_months(trunc(to_date('01-01-2014','DD-MM-YYYY'), 'YYYY'), level - 1), 'DD-MM-YY')) end) as nov_month,
max(case  when rownum=12 then last_day(to_date(add_months(trunc(to_date('01-01-2012','DD-MM-YYYY'), 'YYYY'), level - 1), 'DD-MM-YY')) end) as dec_month
from dual connect by level <= 12 order by rownum;
AND to avoid hardcoding you can also modify your query as :
select
max(case  when rownum=1 then last_day(to_date(to_char(rownum),'MM')) end) as Jan_month,
max(case  when rownum=2 then last_day(to_date(to_char(rownum),'MM')) end) as Feb_month,
max(case  when rownum=3 then last_day(to_date(to_char(rownum),'MM')) end) as mar_month,
max(case  when rownum=4 then last_day(to_date(to_char(rownum),'MM')) end) as apr_month,
max(case  when rownum=5 then last_day(to_date(to_char(rownum),'MM')) end) as may_month,
max(case  when rownum=6 then last_day(to_date(to_char(rownum),'MM')) end) as jun_month,
max(case  when rownum=7 then last_day(to_date(to_char(rownum),'MM')) end) as jul_month,
max(case  when rownum=8 then last_day(to_date(to_char(rownum),'MM')) end) as aug_month,
max(case  when rownum=9 then last_day(to_date(to_char(rownum),'MM')) end) as sep_month,
max(case  when rownum=10 then last_day(to_date(to_char(rownum),'MM')) end) as oct_month,
max(case  when rownum=11 then last_day(to_date(to_char(rownum),'MM')) end) as nov_month,
max(case  when rownum=12 then last_day(to_date(to_char(rownum),'MM')) end) as dec_month
from dual connect by level <= 12 order by rownum;
Easy way: (without connect)
SELECT LAST_DAY (TO_DATE (ROWNUM, 'MM')) AS Jan_month,
       LAST_DAY (TO_DATE (ROWNUM + 1, 'MM')) AS Feb_month,
       LAST_DAY (TO_DATE (ROWNUM + 2, 'MM')) AS Mar_month,
       LAST_DAY (TO_DATE (ROWNUM + 3, 'MM')) AS Apr_month,
       LAST_DAY (TO_DATE (ROWNUM + 4, 'MM')) AS May_month,
       LAST_DAY (TO_DATE (ROWNUM + 5, 'MM')) AS Jun_month,
       LAST_DAY (TO_DATE (ROWNUM + 6, 'MM')) AS Jul_month,
       LAST_DAY (TO_DATE (ROWNUM + 7, 'MM')) AS Aug_month,
       LAST_DAY (TO_DATE (ROWNUM + 8, 'MM')) AS Sep_month,
       LAST_DAY (TO_DATE (ROWNUM + 9, 'MM')) AS Oct_month,
       LAST_DAY (TO_DATE (ROWNUM + 10, 'MM')) AS Nov_month,
       LAST_DAY (TO_DATE (ROWNUM + 11, 'MM')) AS Dec_month
  FROM DUAL
Cheers,
Manik.

Schedule a process chain on last saturday of every month

Hi Guys, My requirement is to schedule process chain on last saturday of every month. Is there any way to do this? I am already aware of below document. So please do not reply with the same. But please let me know if I am missing on anything. I have

How to get last week of the month from a month

Hello, I want to get last week of the month from a month. For example: If Input = April then Output = Week17. Is there any function module? Or what should be the logic inorder to meet such requirement? Thanks in advance for your answers. Nitin.Hi, Ju

First and Last date of any Month.

Hi Friends, I have 2 fields in my table FROM DATE & TO DATE User can enter any date in these two fields, but at time of commit i want to change the FROM DATE to the first date of the Month user have entered. And for TO DATE i want to change the last

Variable last date of previous month/last date of current month

Hello Experts, I am facing an issue while designing a query. Requirement is like this. Report will be run on monthly basis.so on execution of report, it should prompt for month/year. now on report there are two columns for which I have to get data on

How To Run Background Job on Specific Date of Every Month

Hi, I am looking for an option there we can Run Background job On Specific Date! Example: Task Name: Zprg1 > each month of "18", and same I want to repeat after 3 Days means on "22", then want to repeat after 5 days means on "2

How to get last date of the week

hi, how to get last date of the week like FM WEEK_GET_FIRST_DAY gives the date of the first day of the week i need the date of the last day of the week.. thnxdata : p_week type KWEEK, p_Date type SYDATUM. p_week = <incoming value in week of year> CA

RE: FM to get last date of month

Hi, Please tell me if there is a function to return the last day of the month. The input is year+month eg 200710 should return a date 31.10.2007 I noticed that most of the function needs a date, ie DDMMYYYYHi, use RP_LAST_DAY_OF_MONTHS SELECT-OPTIONS

Job trigger on a fix date of every month

Hi All, for program SAPF080R, we want to triger a job where reversal posting date is 5th of every month. So we want to schedule this job on monthly basis where reversal posting date field get value as 5th of every month. Please suggest how we can do

How to get currect date by using month and year

hai, By using month like 'Jul' and year like 2007 i want to get full date like 01/06/2007(format).Please give me query for this?My fields for month and year are respectively vr_mnth(varchar) and in_year(number)i used to_date function but it accepts o

How to get last day of a month

using pl/sql What i need is that all members whose birthdate is in the curren month will be included as having had their birthdate regardless of the date of the month. The code i have is: Select me607.subscriber_id me607.mbr_dob, ** trunc(months_betw

FM to get first date of the month

hi, any FM to get first day of the month ,  lets say based on input 24.11.2008 , the FM would return 01.11.2008Do search in such case(Easy findings). https://www.sdn.sap.com/irj/scn/advancedsearch?query=fmtogetfirstdayofthemonth&cat=sdn_allRead other

JDeveloper 10.1.2 - ADF BC - get last date from a view in validation

Hello I have a list of subscribers. One person can be several times in this list with start and end dates. How can I get the last end date when I try to add a new row to validate it?Thanks. I tried to use the alternative writeXml(long, HashMap) metho

Converting Period to 1st and last date of that month

Dear all, is there any FM to to do the following:- if in the selection screen i give the date as 09.2007 (MM.YYYY) then i want the output as 01.09.2007 to 30.09.2007 If u go to transaction MC.1  in that u will find this as select options. So when i e

Dyamic execution of report for every month and data through mail.

Dear Friends, We are using ECC6.00 With EHP4. My requirement is to send the list of pending notifications with the list details as appearing in transaction QM10. Every month i am supposed to change the from date and to date for example 01.07.2010 to

Last date of month

Hi im new to java. Looking for a class that provides last date of the month according to a given year and month .java does provide a calendar object for gregorian dates.. surprising its called GregorianCalendar. :PAs I said avoid GregorianDate in Jav

ICal 3.0: Last day of the month repeating event

I am migrating back to iCal after a few years using Entourage. I am trying to set up calendar events and reminders for the last day of every month, but I can't figure out how to do it. In Entourage this was easy. Any advice? Thank you.I would like th

TO FIND START DATE AND END DATE OF THE MONTH

HAI ALL,            I want to find the start of the date for the month and end of the date for the month, for given date. Ex. if input is : 08-06-2007,                                        I want to get the first and last date for this month. . Is

Need formula in my subreport to get last month date - CR XIr2

Post Author: olesco CA Forum: Formula I need to pass the value (#of units) from the subreport to the main report u2013 I know how to do that but my problem is I donu2019t know what formula should I put in my subreport  to get last month date. Both ma

How to get LASTDAY for each and every month between given dates..

Hi Friend, I have a doubt,How to get LASTDAY for each and every month between given dates.. for ex: My Input will be look like this from date = 12-01-2011 To date = 14-04-2011 And i need an output like 31-01-2011 28-02-2011 31-03-2011 is there any wa