Using date in Report definition

[Former Member]

Hi guys, I am trying to create a Report definition with below query. But however I am getting the attached error message whenever I execute the Report definition.

Query: SELECT {budget} FROM {Subscription} WHERE {modifiedtime} >= ?startDate

Question: Is it due to the date format of the modified time or date from the date picker. I tried creating a report with some other type pf columns and found it is working but not with the date.

Please help me to solve this issue. Some thing related to the query I am using.

Thanks Sethu

[Former Member]

: Can you please share the query you used to resolve this issue? I am working on a similar requirement.

[Former Member]

No, It dint help me. Please see the date format in the screenshot I uploaded. I believe that should be the date format which i am getting in my query as startDate parameter. I want to convert this to a good date format so that i can run my query successfully.

need to use something shown as below:

SELECT {pk} FROM {Product} WHERE {modifiedtime} >= TO_DATE('Jan 9, 2019 11:37:46 AM','......')

[arvind-kumar_avinash]

I have updated my answer as per your requirement. Please check and feel free to let me know if you need any further support for this question.

[arvind-kumar_avinash]

What is the format of the date you are passing as the parameter? The default date format in FS Query is "yyyy-MM-dd HH:mm:ss". You can specify your own format using the DBMS function, TO_DATE e.g

 SELECT {pk} FROM {Product} WHERE {modifiedtime} >= TO_DATE('2019/01/03','YYYY/MM/DD')

You can use many kinds of combinations in format e.g. if you want to use a date, something like Jan 9, 2019 11:37:46 AM, you can do it like:

 SELECT {pk} FROM {Product} WHERE {modifiedtime} >= TO_DATE('Jan 9, 2019 11:37:46 AM','Mon DD, YYYY HH:MI:SS AM')