Cognos 8.3 – Date prompt Error Microsoft SQL database

Recently, we had an issue with date prompts. When we use regular date prompts, one each for start and end, the reports were running fine. But later when we had to test the report on ‘Microsoft SQL’, we faced some issues.
Something like: -69 , -9, -201 Errors and sometimes
‘prepare’ [Microsoft ODBC] and some crappy message.
Initially, I had come out with a simple solution, that is to keep the date prompt in ‘in _range‘ and the report used to work. But we could not make too many changes with the ‘in_range’ prompt. Then I found out that, because Microsoft uses a different date format we need to cast the filter.
Example - [date] between cast(?begdate?, timestamp) and cast(?enddate?,timestamp)

Even if we use cast into ‘Date’, the prompt does not seem to work.
The main problem one faces in this situation is identifying the issue, The errors are so strange and are not at all related to the issue sometimes.

Never took the screen shot of the issue and now too lazy to make the changes and put in the screen shot.