I would like to include the current date, today(), as a query item in the model. The reason I am trying to do this is because I want to then be able to use this query item as the default value in a prompt or use it in a filter.
For example, I want to run a report of current enrollment. I should not have to prompt for the current session. I can calculate it by filtering on:
getdate() between acad_cal_rec.beg_date and acad_cal_rec.end_date
I am getting an error when I attempt to do this. If I attempt to create a new query item and use the function: getdate() I receive the following error:
RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-126'.
UDA-SQL-0129 The function "getdata" is being used for local processing but is not available as a built-in function, or at least one of its parameters is not supported.
I tried both "Limited Local" and "Database Only" Query Processing on the datasource. I receive the same error with both settings.
Thanks for any help!
getdate() is T-SQL, Use the Cognos current_date
The problem is that EACH valid SQL statement needs to address at least 1 table or view. SQL Server is AFAIK the only database where you can skip the from part , like in
Select getdate()
Simply using a dataitem with current_date within a report works just fine..
Great - thanks for the reply. I am using SQL Server - both the content store and the source db. I will see if I can tweak it and get it to work.