getting first date and last date of previous month in oracle

Hi all,
How can i get first date and last date of previous month.
Please help me.


Thanks
Rajneesh

You can use TRUNC function to do that.

select trunc(trunc(sysdate,'MM')-1,'MM') "First Day of Last Month",trunc(sysdate,'MM')-1 "Last Day of Last Month" from dual

hai all
how can i get a patricular day of 6 year before

hai all

how can i get all null values of table

Please open a separate thread for a separate question. It sounds like you need a tutorial on SQL. Databasejournal has a forum called Structured Query Language for that.

Quote:

---

Originally Posted by devanasamy

hai all
how can i get a patricular day of 6 year before

---

solution:
-------------:cool:
SELECT sysdate,add_months(sysdate,-12*6),to_char(add_months(sysdate,-12*6),'Day') FROM dual

Quote:

---

Originally Posted by devanasamy

hai all

how can i get all null values of table

---

solution:

Using IS operator we can fetch null values.

Select * from t1 where c1 is NULL.

Quote:

---

You can use TRUNC function to do that.

select trunc(trunc(sysdate,'MM')-1,'MM') "First Day of Last Month",trunc(sysdate,'MM')-1 "Last Day of Last Month" from dual

---

this works great but how can I make it read 30-APR-2009

select to_char(trunc(trunc(sysdate, 'MM') - 1, 'MM'),'DD-MON-YYYY') "First Day of Last Month",
to_char(trunc(sysdate, 'MM') - 1,'DD-MON-YYYY') "Last Day of Last Month"
from dual

I am not an expert with Oracle, can someone please provide me with the proper Where syntax that would return records between "First Day of Last Month" and "Last Day of Last Month".

I tried this but it was not returning any results:

Select * from mytable
Where datefield between trunc(trunc(sysdate,'MM')-1,'MM') and trunc(sysdate,'MM')-1

Thank you.

Probably your table (mytable) has no rows!
If you use the dual table, your are guaranteed to have 1 row.

Quote:

---

Originally Posted by brunobiondo

Probably your table (mytable) has no rows!
If you use the dual table, your are guaranteed to have 1 row.

---

Sorry, I was not exhaustive.
Your table (mytable) either is empty or it doesn't have any row that mach the specified condition in your query.
:)

Quote:

---

Originally Posted by brunobiondo

Sorry, I was not exhaustive.
Your table (mytable) either is empty or it doesn't have any row that mach the specified condition in your query.
:)

---

...and forget I told you to use dual, dual return always 'X' (1 row)
:D

for start date of previous month :

DATEADD("m",-1,DATEADD("D",-(DAY(Today())-1),Today()))

for last date of prvious month :

DATEADD("D",-(DAY(Today())),Today())

first day of the month can be got using trunc
Trunc to the first of the month:
e.g:
TRUNC (TO_DATE ('12-MAR-1994'), 'MONTH') ==> 01-MAR-1994
TRUNC (TO_DATE ('17-MAR-1994'), 'MM') ==> 01-APR-1994


last day of the month can be obtained using function

LAST_DAY('DD-MM-YYYY'):)

Quote:

---

Originally Posted by GesuChandra

for start date of previous month :

DATEADD("m",-1,DATEADD("D",-(DAY(Today())-1),Today()))

for last date of prvious month :

DATEADD("D",-(DAY(Today())),Today())

---

This doesn't look like Oracle SQL to me.

But this is how I would take a similar approach to find the start and end of a previous month:

select trunc(add_months(datefield, -1), 'MONTH') first_day,
last_day(trunc(add_months(datefield, -1), 'MONTH')) last_day
from mytable;