Setting prompt value with _first_of_month

[parkh2]

Hi,

I imported a SQL Server stored procedure into my FM model which accepts 'startDate' and 'endDate' parameters, with both being a 'varchar(10)' data type.  I want to set the default values of the startDate and endDate parameters to be set to the first of the current month and the last of the current month, respectively.  So, when I go to edit the definition of the stored procedure, I entered the following for the prompt arguments:

For the 'startDate' parameter:

Code Select Expand

cast(#prompt('@startDate', 'Date', '_first_of_month(current_date)')#, 'nVarChar(10)')

For the 'endDate' parameter:

Code Select Expand

cast(#prompt('@endDate', 'Date', '_last_of_month(current_date)')#, 'nVarChar(10)')

However, once I publish the package the default values get set to the current date.  Does anyone know why this is happening?

[AnalyticsWithJay]

Syntax is incorrect. This syntax works:

Code Select Expand

cast(#prompt('@FromDate','date', '_first_of_month (current_date)')#, varchar(10))

Also, note Cognos' documentation about #prompt macro for stored procedures and dates:

The "defaultText" parameter must be specified such that it is literally valid in the context of the macro since no formatting takes place on this value. The default string '''Canada''' in Example 2 is specified as a string using single quotes, in which the embedded single quotes are doubled up, thus 3 quotes. This results in the string being properly displayed within single quotes in the expression. As a general rule for the string datatype, "defaultText" should always be specified like this, except in the context of a stored procedure parameter. For "defaultText" of types 'date' or 'datetime', a special format should be used in the context of SQL. Examples of these formats are 'DATE ''2001-12-25''' and 'DATETIME ''2001-12-25 12:00:00'''. In all other contexts, use the date/datetime without the keyword and escaped single quotes (e.g., '2001-12-25').