Previous month entire date's in the select query?

Database

Previous month entire date's in the select query?

How to get the last month entire date's in the select query?

Ex: I need to display the last month entire date's from the beginning to the end

08/01/2015

08/02/2015

08/03/2015

08/04/2015

...

08/31/2015

Any one help!

5 REPLIES
Enthusiast

Re: Previous month entire date's in the select query?

Hi Dev,

Please try this..

SELECT calendar_date from calendar where  calendar_date between ADD_MONTHS(current_date - EXTRACT(DAY FROM current_date)+1, -1)
and (current_date - EXTRACT(DAY FROM current_date)) order by 1;

Hope this helps!

Re: Previous month entire date's in the select query?

Hi Sakthi, 

Am getting error says Object "calendar_date " not exist

What I have to do?

~Dev

Enthusiast

Re: Previous month entire date's in the select query?

The SYS_CALENDAR.CALENDAR system view helps to extend the properties of a DATE data type column by means of a join. The CALENDAR system view is defined for the years 1900 - 2100. 

To access SYS_CALENDAR.CALENDAR, you must have SELECT privileges.

Try this..

SELECT calendar_date from SYS_CALENDAR.Calendar where  calendar_date between ADD_MONTHS(current_date - EXTRACT(DAY FROM current_date)+1, -1)
and (current_date - EXTRACT(DAY FROM current_date)) order by 1;

Re: Previous month entire date's in the select query?

thanks sakthi.

Enthusiast

Re: Previous month entire date's in the select query?

You can use the following command to get the previous month entire date range#

Processing

Processing_Date  BETWEEN (CAST(((ADD_MONTHS(DATE, -1)/100)*100+1) AS DATE)) AND ( DATE-DATE MOD 100)