Hello,
I am trying to use an optional prompt by using a default value /w MSSQL. It works for int, varchar etc.... but I can not get it to work /w dates for the life of me. What am I missing? I like to delcare and set prompts as I may use the promp multiple time and it is easier to test things, but to exclude that as the problem, I tried without doing that also and couldn't get it to work that way either.
DECLARE @Prompt1 VARCHAR(25), @Prompt2 VARCHAR(50), @Prompt3 INT, @Prompt4 VARCHAR(50), @Prompt5 DATETIME
SET @Prompt1 = #prompt('@Prompt1', 'string','''All''')#
SET @Prompt2 = #prompt('@Prompt2', 'string','''All''')#
SET @Prompt3 = #prompt('@Prompt3', 'int','999')#
SET @Prompt4 = #prompt('@Prompt4', 'string','''All''')#
SET @Prompt5 = #prompt('@Prompt5', 'date','2000-01-01')#
select blah
from blah
WHERE (BusGrp = @Prompt1 OR @Prompt1 = 'All')
AND (TestValue2 = @Prompt2 OR 'All' = @Prompt2)
AND (MONTH(TestValue5) = @Prompt3 OR 999 = @Prompt3)
AND (ResolveDsc= @Prompt4 OR @Prompt4 = 'All')
AND (TestValue5 = @Prompt5 OR @Prompt5 = '2000-01-01')
Tried without variable
AND (TestValue5 = #prompt('TestValue5','date','2000-01-01')# OR #prompt('TestValue5','date','2000-01-01')# = '2000-01-01')
also tried which ran but gave no results where results where expected
AND (TestValue5 = #prompt('TestValue5','date','2000-01-01')# OR #prompt('TestValue5','date','2000-01-01')# = CAST('2000-01-01' AS DATETIME))
You don't say what the error is, but you can just pass it as a text value and then convert it in on the database side.
The error was it couldn't convert string to datetime. Which is why I tried it using cast as, but all I get is 0 results when I have no error.
Quote from: calson33 on 07 Oct 2013 02:13:25 PM
You don't say what the error is, but you can just pass it as a text value and then convert it in on the database side.
Now I get it! So basically cognos makes you use the prompt as a string which is the opposite of what I would "think" would work. Works great now! Thanks!