Optional SQL Date Prompt

[Dulanic]

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.

Code Select Expand

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

Code Select Expand

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

Code Select Expand

AND (TestValue5 = #prompt('TestValue5','date','2000-01-01')#  OR #prompt('TestValue5','date','2000-01-01')#  = CAST('2000-01-01' AS DATETIME))

[calson33]

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.

[Dulanic]

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.

[Dulanic]

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!