Filtering SQL dates in Nettalk Reports

[kboller]

Hi,

I was getting spurious results with the following filter where I would get a record with a date outside of the range (typically the lower end)

     StartAssmtDate and EndAssmtDate are session variables provided by the end user for the report date range

     loc:MyReportFilter ='ASMT:AsmtDate >= '&StartAssmtDate&' and ASMT:AsmtDate <= '&EndAssmtDate

I found a post by Shankar from the news groups that said SQL queries work best when using the YYYY-MM-DD format (@D10-) and after changing my query to the following, everything started working as expected.

     loc:MyReportFilter ='format(ASMT:AsmtDate,@D10-) >= '&''''&format(StartAssmtDate,@D10-)&''''&' and format(ASMT:AsmtDate,@D10-) <= '&''''&format(EndAssmtDate,@D10-)&''''

I hope that helps someone.

Kurt Boller