All Forums Database
samit_9999 37 posts Joined 03/06
25 Apr 2007
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 abcwhere DOB between (DATE - extract(day from DATE)) and ADD_MONTHS(DATE - EXTRACT(DAY FROM DATE),-13)+1Is there a better way to write this query?Thanks,Sam

joedsilva 505 posts Joined 07/05
26 Apr 2007

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)+1and (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)+1andDATE - DAYOFMONTH ;That won't change any plans though :-) just a convenient way of reusing expression.

samit_9999 37 posts Joined 03/06
26 Apr 2007

Yes you are right i messed up with the order

23 Aug 2012

SEL current_date-extract(day from current_date);

You must sign in to leave a comment.