Dynamic Date Range

[PB3]

Hi folks.

I'm building a simple query that will be scheduled to run at a particular date in the month. I want this query to pull the previous months data. I've done some research and found that creating a filter was the best way to go.

Based on what I read I tried putting the below logic in the filter but I'm getting the following error message "invalid token 'date' found after "[Invoice Date] between""

Any help would be greatly appreciated!

Here's what The logic I have in the filter:

[Invoice Date] between date add(month,-1,_first_of_month(get date()))and date add(month,-1,_last_of_month(get date()))


Sent from my iPhone using Tapatalk

[PB3]

I dug around and found this thread that looked like the OP was trying to accomplish something similar to what I'm after.

Date prompts and scheduled reports

https://r.tapatalk.com/shareLink?url=http%3A%2F%2Fwww%2Ecognoise%2Ecom%2Fcommunity%2Findex%2Ephp%3Ftopic%3D31562&share_tid=31562&share_fid=17462&share_type=t

I changed up my filter logic based on a link that was provided by BigChris. Here what I have now.

[Invoice Date] between cast(_first_of_month(_add_months(getdate(),-1)),date) and cast(_last_of_month(_add_months(getdate(),-1)),date)

Still doesn't work but am I on the right path? I don't understand the 'cast' or 'getdate' part of this logic.

I guess I should've pointed out I'm not a report writer. Just a plain old financial analyst with less than a year of experience of building queries in cognos. Any guidance would be much appreciated. :)


Sent from my iPhone using Tapatalk

[clamus17]

Hi PB3,

The Cast function converts the expression to a specified data type (in your example you're converting the getdate() value to date which is already a date) while Getdate gets the current date and time (This function is for SQL Server database only).

Try using the current_date function instead of getdate() and you may try removing the cast function.

Let us know if you still have any issues.

Cheers! :)