getting first date and last date of previous month in oracle

[Osho4U]

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


Thanks
Rajneesh

[skhanal]

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

[devanasamy]

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

[devanasamy]

hai all

how can i get all null values of table

[skhanal]

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.

[srikanth.mss]

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

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

[srikanth.mss]

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.

[gottogo]

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

[drsam]

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

[jhenri]

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.

[brunobiondo]

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

[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.

[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)

[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())

[PANTHER3]

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')