# Database Discussions > Oracle >  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:
------------- :Cool: 
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.
 :Smilie:

----------


## 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)
 :Big Grin:

----------


## 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') :Smilie:

----------


## jnjulian

> 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;

----------

