error in if statement

[philelmousse]

Hello,

I created a filter in my query subject but there is an error. Are you able to find it ?

The error is on first line, at "<=3" statement

Code Select Expand


case
when (extract(year from gl_account.valid_from) = 2011) then (extract(month from gl_account.valid_from) <= 3)
else (extract(year from gl_account.valid_from) <= 2011) end
and
((extract(year from gl_account.valid_to) >= 2011) and (extract(month from gl_account.valid_to) >= 3))


Thanks

[rockytopmark]

Your use of Extract must be in syntax of your specific rdbms rather than Cognos, as here is Extract() function's syntax for Cognos:

extract ( datepart , datetime_exp )
Returns an integer representing the value of datepart (year, month, day, hour, minute, second) in "datetime_exp".

hth

[philelmousse]

I create a timestamp with Month prompt and year prompt like this :

_make_timestamp (  cast(?year?,int),cast(?Month?,int),0)

I use that calculation item to filter a query calculation on a date column and get that error :

UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. (SQLSTATE=22007, SQLERRORCODE=242)

[Lynn]

The make timestamp function has three arguments: year, month, day. Your expression looks like it is trying to use day number 0 which would not translate to a valid date.