Last Day of Previous Month

Database
Enthusiast

Last Day of Previous Month

Hi ,

I want to write a query to select data where the DOB falls between a 13 month period.

Example if i run this query today (25-Apr-2007)
It should select data where DOB between 1st Feb 2006 and 31st Mar 2007(Last day of the previous month)

SELECT *
from abc
where DOB between (DATE - extract(day from DATE)) and
ADD_MONTHS(DATE - EXTRACT(DAY FROM DATE),-13)+1

Is there a better way to write this query?

Thanks,
Sam
3 REPLIES
Enthusiast

Re: Last Day of Previous Month

Other than that you should reverse the order of the between conditions, I don't see any trouble. otherwise you won't get any records.

where DOB between
ADD_MONTHS(DATE - EXTRACT(DAY FROM DATE),-13)+1
and
(DATE - extract(day from DATE))

Of course you can use "NAMED" to reuse expressions like this.

where DOB between
ADD_MONTHS(DATE - (EXTRACT(DAY FROM DATE) ( NAMED "DAYOFMONTH" )), -13)+1
and
DATE - DAYOFMONTH ;

That won't change any plans though :-) just a convenient way of reusing expression.
Enthusiast

Re: Last Day of Previous Month

Yes you are right i messed up with the order

Re: Last Day of Previous Month

SEL current_date-extract(day from current_date);