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
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
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! :)