LastFullMonth date function in Oracle 1

[lana123]

Hi,people.
I use Date function LastFullMonth in Crystal Report 9.2.
Does it exist the identical function for Oracle(I work with Oracle 8) or I have to do some calculations.

I'll appreciate any answer.
Thanks,

Lana

 

[SantaMufasa]

Lana, give us a hint...what does a "LastFullMonth" function do in Crystal Reports? If you can give us a sample invocation of the function and its arguments, along with expected results, then if Oracle doesn't have a similar function, we can build one for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Turkbear]

Hi,
Lana also posted this in the Crystal forum, so I will help out and post this here since it is really an Oracle question:

In Crystal:

LastFullMonth - Specifies a range of Date values that includes all dates from the first to last day of the previous month.


So,Dave, have at it..( Your Sql is better then mine..)





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SantaMufasa]

Thanks, Turkbear. Can you post a sample invocation, including arugments, and the expected results from that invocation?

Thanks!

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[lana123]

Hi,Dave.
Sorry,didn't explain properly.
The function LastFullMonth gives in WHERE close the results as

Select *
from Table1
where Table1.Date >=TO_DATE ('01-09-2005','DD-MM- YYYY') AND Table1.Date < TO_DATE ('01-10-2005 ', 'DD-MM-YYYY') if I will use it in October.

Thank you very much,
Lana

 

[SantaMufasa]

So, Lana,

I still don't see how your Crystal Reports "LastFullMonth" function works, but if you want to know how, in Oracle, see if a data falls between the first and last days of the month prior to the current month, you could say:

Select *
 from Table1
 where Table1.Date
    between trunc(
               trunc(sysdate,'MM') -- first day, this month
               -1 -- sub 1 day to last day of previous month
               ,'MM') -- truncates to first day, last month
        and
            trunc(sysdate,'MM') -- first day, this month
              -(1/24/60/60) -- subtracts 1 second, to last
              -- second of last day of previous month
;

Let us know if this resolves your need.


Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[lana123]

Thank you for help.
I'll work with it right now.
Lana

 

[SantaMufasa]

Also, Lana, if my earlier suggestion is too "messy" for all the places you want to use it, we could create a function that works like this:

Select *
 from Table1
 where in_prev_month(Table1.Date,sysdate) = 'Y'
;

The "in_prev_month" function would allow you to put in any date as <argument1> (in place of "Table1.Date") and check to see if that date is anywhere in the month prior to <argument2>. If it is, then the function returns a "Y"; otherwise it returns an "N".

Let me know if this interests you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[lana123]

Yours query works well.
But - yes -to have "in_prev_month" function would be great!
With a lot of thanks

Lana

 

[SantaMufasa]

Here is your "in_prev_month" function, as we specified:

create or replace function in_prev_month
    (date_in date,month_in date)
    return varchar2
is
begin
    if date_in
        between trunc(trunc(month_in,'MM')-1,'MM') and
                trunc(month_in,'MM')-(1/24/60/60) then
        return 'Y';
    else
        return 'N';
    end if;
end;
/

Function created.

Here are some sample data:

col a heading "Dates"
select to_char(dt,'dd-MON-yyyy "@" hh24:mi:ss') a
from table1
order by dt;

Dates
----------------------
28-AUG-2005 @ 14:38:45
01-SEP-2005 @ 00:00:00
17-SEP-2005 @ 14:38:44
27-SEP-2005 @ 14:38:44
30-SEP-2005 @ 23:59:59
27-OCT-2005 @ 14:38:44

6 rows selected.

And here are a couple of invocations:

Select to_char(dt,'dd-MON-yyyy "@" hh24:mi:ss') a
  from Table1
 where in_prev_month(Table1.Dt,sysdate) = 'Y'
 order by dt;

Dates
----------------------
01-SEP-2005 @ 00:00:00
17-SEP-2005 @ 14:38:44
27-SEP-2005 @ 14:38:44
30-SEP-2005 @ 23:59:59

4 rows selected.

Select to_char(dt,'dd-MON-yyyy "@" hh24:mi:ss') a
  from Table1
 where in_prev_month(Table1.Dt,sysdate-30) = 'Y'
 order by dt;

Dates
----------------------
28-AUG-2005 @ 14:38:45

1 row selected.

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.