Dynamically Determine Date Range Based on System Date

Database

Dynamically Determine Date Range Based on System Date

Hello,

I am trying to schedule a report that will run on a monthly basis.  I am using Business Objects as the front end and Teradata as the backend database.  I would like to generate the date range dynamically based on the system date.  I am enclosing an example of what I am trying to do below.  I have tried a few things so far, but it has not worked. Any assistance you could provide would be much appreciated.

calendar.first_day_of_month >= 1st day of last month minue 1 year (Example September 01, 2012)

calendar.first_day_of_month <= the last day of the last month (Example August 31, 2013)

Thanks

Tags (1)
5 REPLIES
Enthusiast

Re: Dynamically Determine Date Range Based on System Date

Hi,

You can use the following query. 

SELECT  CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE) AS PREV_MONTH , (ADD_MONTHS(PREV_MONTH, -12) +1)  PREV_MONTH_LAST_YEAR;

The ADD_MONTH Function perform date calculation intelligently and take care of days of months.

Khurram

Re: Dynamically Determine Date Range Based on System Date

HI Khurram, Thanks very much for your response.  I am still having an issue make this work in Business Objects. Do you happen to know the syntax I need to use with that to make it work in Business Objects?  I think it would looks something like this:

Calendar.first_day_of_month <= (select cast(cast( CAST( EXTRACT(MONTH FROM CURRENT_DATE) AS CHAR(2))||'01' as date format 'yyyymmdd') as date format 'yyyy-mm-dd'))

I have tried it with your example, but have not been able to get it to work. 

Thanks much!

Re: Dynamically Determine Date Range Based on System Date

Here are the two values I am trying to generate in Business Objects:

calendar.first_day_of_month >= 1st day of last month minue 1 year (Example September 01, 2012)

calendar.first_day_of_month <= the last day of the last month (Example August 31, 2013)

I think it should look something like this:

Calendar.first_day_of_month <= (select cast(cast( CAST( EXTRACT(MONTH FROM CURRENT_DATE) AS CHAR(2))||'01' as date format 'yyyymmdd') as date format 'yyyy-mm-dd'))

Thanks much!  I would love to get this resolved.

George

Enthusiast

Re: Dynamically Determine Date Range Based on System Date

Hi,

I have not done much work in Business Objects. You cant refer to the following blog, and I am sure it contains info you need.

http://bobjblog.wordpress.com/tag/formatdate/

Khurram
Junior Contributor

Re: Dynamically Determine Date Range Based on System Date

Assuming "calendar" is a table in Teradata:

calendar.first_day_of_month BETWEEN ADD_MONTHS(CURRENT_DATE - (EXTRACT(DAY FROM CURRENT_DATE)-1),-12)
AND CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)

Dieter