regarding date functions

[kkr12]

Hi,
I'm trying to use sql function to get last day of last month. Say , if x = '01-OCT-2001' , then I want y = '30-sep-2001'.

In other case, I've a number field A = 200103 which represents March,2001. Then I've a date field called B = '01-APR-2001'.

Actually I want to write a sql statement joing these two fileds.

So, I'm using something like this:

select * from .....
....
where A = to_number(to_char(B,'yyyymm)-1);

This seems to be working. But what happens if A = '01-JAN-2001' ?

Can some one help me to write a query which gives me the last month ?

Thanks.

[Raminder]

Oracle has a built in function called last_day(date) which gives the last date of the month of date.

so for getting the last date of previous month, you can simply do:

Select last_day (B-2) from dual;

where B is the first day of your month.

[PSoni]

hi

use add_months function


Select Add_months(date, -1) from dual



PSoni

[nealh]

What about:

select * from .....
....
where A = to_number(to_char(trunc(B, 'mm')-1), 'YYYYMM'));

[PSoni]

What about:
where A = to_number(to_char(trunc(B, 'mm')-1), 'YYYYMM'));


in this case there may be problem when month is january

Psoni

[nealh]

Why ? If I've understood the problem correctly, kkr12 wants to equate a date in the current month with the previous month in YYYYMM format.

So, if A='03-JAN-2002', B=200112 then

trunc(a, 'mm') = '01-jan-2002'
trunc(a, 'mm') - 1 = '31-dec-2001'
to_char('31-dec-2001', 'YYYYMM') = '200112'
to_number('200112') = 200112



[Edited by nealh on 10-18-2001 at 08:20 AM]

[PSoni]

Hi nealh


Again u r right. Previously I had not tested ur solution. I was using only Add_months.

thanks


P Soni