Cognos Tech Tip: SQL Functions in Cognos

SQL Functions in Cognos

If you have tried using certain SQL functions that you know should work but you get errors…you may have to modify your syntax slightly for Cognos.

Example: dateadd(mm, 1, getdate())
Result in SQL Server Management Studio: Mar 16, 2015 12:19:27 PM
Result in Cognos: Parsing error before or near position: 12 of: “dateadd(mm,”

Solution: You must wrap the “mm” in curly brackets “{}”
Working Query Calculation: dateadd({mm}, 1, getdate())

Result:

Of course you can cast it if you want just the date. Cast(dateadd({mm}, 1, getdate()), date)

 

This works with MANY SQL functions that you may struggle with.  Hope that helps!

Cheers!