SQL function TO_DATE() is not working in SAP MII SQL Fixed Query template

[avisheksharmacoder]

The query is

Select * from Queue_name

where

Queue_name.CREATED > to_date('08/08/2013 23:59:59', 'MM/DD/YYYY HH24:MI:SS')

This query is returning this error

CREATED > to_date('08/08/2013 23:59:59', 'MM/DD/YYYY HH24:MI:SS')" contains the syntax error[s]: - 3:33 - SQL syntax error: the token "(" was not expected here

How to solve this error in the SQL Query template or is there a different way around this

Thank you.

[steve_stubbs]

Hi Avishek,If you execute the query without a where clause, what date/time format of the Queue_name.CREATED Column returned?If you output to Browser as text/xml, what is the SQL Data Type of the CREATED column?Regards, Steve

[steve_stubbs]

Hi Avishek,

I don't think you need to use to_date here. Try this:

in the Sql Query Parameters create a typed parameter of type Timestamp for Param.1

enter the value for the parameter in the format MM/dd/yyyy HH:mm:ss, i.e. 08/08/2013 23:59:59

change your where clause to Queue_name.CREATED >[Param.1]

Regards, Steve

[avisheksharmacoder]

Hi Steve.

It works.

But the issue is I cannot use any function in the SQL Query Template. Direct Queries work fine. Is that a system issue ? I have tried this to_date() and also the CONCAT() function as well. None works.

[agentry_src]

What database are you querying and version?

[suresh7]

The date conversion in the database queries will depend on the type of backend database you are using. The functions has to be support by the database itself.

[avisheksharmacoder]

Hi Mike.

The Database at the backend, is OpenSQL.