Campaign Report using Offer Schema - Help

[wpomeroy]

I am trying to build a report that shows information from our offer schema (label, start date, end date, HTML Rendering, etc.) based on use input of offer name and date.  I.e. user enters the offer name or start and end date and they are able to see the offer information.

I am defining local variables and then using the query to filter by the defined variables.  Searching by offer label works fine but start or end date throws the following error.

ORA-210000 Oracle error: ORA-00932: inconsistent datatypes: expected DATE got NUMBER WDB-200002 SQL statement 'SELECT O0.sLabel, O0.sName, O0.tsEnd, O0.tsStart, O0.sCode, X1.sLabel FROM NmsOffer O0 JOIN XtkFolder X1 ON (X1.iFolderId = O0.iCategoryId) WHERE (O0.sLabel LIKE :1 || :2 || :3 ESCAPE '\') AND (O0.tsStart <= trunc(:4, 'ddd')) AND ((X1.sName LIKE :5 || :6 || :7 ESCAPE '\') AND (O0.sLabel NOT LIKE :8 || :9 || :10 ESCAPE '\') AND (O0.sLabel NOT LIKE :11 || :12 || :13 ESCAPE '\') AND (O0.sLabel NOT LIKE :14 || :15 || :16 ESCAPE '\'))' could not be executed (error in position 207: '<='). Param(0)=% Param(1)=WP Param(2)=% Param(3)=2020-06-29 Param(4)=% Param(5)=livePERD Param(6)=% Param(7)=% Param(8)=Test Param(9)=% Param(10)=% Param(11)=test Param(12)=% Param(13)=% Param(14)=TEST Param(15)=% SOP

I have tried defining both the variables and the offer fields explicitly as dates (ToDate) without success.  I havce also tried leveraging a script activity to format the date.  Thus far nothing has worked.  Any help would be MUCH appreciated.

Below is my report setup

[Jonathon_wodnicki · Accepted solution]

Hi,

Use ToDateTime() instead of ToDate().

Thanks,

-Jon

[wpomeroy]

This worked, thank you! I also had to change the offer start date field (non local variable) to DateOnly so that it the time stamps would match. This allowed me to say vars/startDate = @startDate