PL/SQL LAST_FULL_MONTH Function

[Sahubba]

Hello,
Is it posible to create a last full Month function in Sql?

If so can someone please help me?

Here is the code that I am trying to make into a function
add_months((last_day(sysdate))+1,-2) AND add_months((last_day(sysdate)),-1)


Thank You!

 

[SantaMufasa]

Sahubba,

I'm certain that we can suggest a function for you. Can you please clarify, with examples, the functional (not syntactical) narrative of what you want the function to do? Include an explanation of the algorithms that you want the function to follow.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[Sahubba]

Hi SantaMufasa,
Thank you for your responds!

I would like to create a function call LastFullMonth (Something similar in crystal)that will give me all the records between the 1st day and last day of last month.

So instead of writing out

select * from (table.t)
where t.Date between add_months((last_day(sysdate))+1,-2) AND add_months((last_day(sysdate)),-1)

I would be able to write t.Date n LastFullMonth like you have the option to do in crystal reports.


I hope this makes sense.

Thanks

 

[SantaMufasa]

Sahubba,

First of all, in checking your posted logic, your results (whether in Oracle SQL or Crystal Reports) will not give you the results you want if t.Date's time is between midnight and the current time on last month's Day 1 or between the current time and midnight on the last day of last month. (If you are not sure why this is the case, please post, and we can post an explanation of why.)

Since Oracle SQL is not able to use boolean results from a function, we can still create a function that returns the logical equivalent of a boolean:

CREATE OR REPLACE FUNCTION LastFullMonth (DT_IN DATE) return varchar2 IS
BEGIN
    if dt_in is null then
        return 'NULL';
    end if;
    IF DT_IN BETWEEN TRUNC(ADD_MONTHS(SYSDATE,-1),'mm') -- Midnight on Day of last month
                 AND TRUNC(SYSDATE,'MM')-(1/24/60/60)   -- 11:59:59 of last month
            THEN
        RETURN 'TRUE';
    ELSE
        RETURN 'FALSE';
    END IF;
END;
/

Then, to use the above function in your code, you can invoke with the following:

select * from <table_name>
 where LastFullMonth (<date_column>) = 'TRUE';

Let us know if you have questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[Sahubba]

Hi Mufasa,

Thanks for the code.
I am not too sure if it does work.

I enter this code based on the function code
select * from table
where LastFullMonth(date) = 'TRUE'

and it just runs until it times out.

 

[SantaMufasa]

Sahubba,

The code looks like it works just fine. Here is a proof of concept using employee-start-date information:

select last_name,start_date
  from s_emp
order by start_date;

LAST_NAME           START_DATE
------------------- ----------
Giljum              19-DEC-11
Urguhart            19-DEC-11
Nguyen              23-DEC-11
Maduro              08-JAN-12
Nozaki              10-JAN-12
Catchpole           10-JAN-12
Sedeghi             19-JAN-12
Velasquez           28-JAN-12
Havel               28-JAN-12
Ropeburn            03-FEB-12
Ngao                07-FEB-12
Smith               07-FEB-12
Dancs               16-FEB-12
Quick-To-See        08-MAR-12
Biri                08-MAR-12
Schwartz            09-APR-12
Magee               14-APR-12
Menchu              14-APR-12
Markarian           26-APR-12
Nagayama            18-MAY-12
Newman              21-JUN-12
Patel               07-JUL-12
Dumas               09-SEP-12
Patel               17-SEP-12
Chang               31-OCT-12

25 rows selected.

select last_name,start_date
  from s_emp
 where LastFullMonth(start_date) = 'TRUE';

LAST_NAME           START_DATE
------------------- ----------
Dumas               09-SEP-12
Patel               17-SEP-12

2 rows selected.

Elapsed: 00:00:00.01

It returns in 1/100th of a second, so I don't see any delay/timeout issues.

To test your code without any table concerns, try this test code:

select 'This works fine.'
  from dual
 where LastFullMonth(sysdate - 20) = 'TRUE';

'THISWORKSFINE.'
--------------------
This works fine.

1 row selected.

Elapsed: 00:00:00.03

The above code, reading from DUAL, takes 3/100ths of a second, so reading from a table should not be a big issue. Please advise the outcome of your experiment.





Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.