How to Query The Date Filed in OData?

[Former Member]

I have the following entityset in my OData:

EFFECTIVE_DATE and EXPIRY_DATE have Edm.DateTime as datatype.

I can Query this OData service using filter as :

$filter= DEALER_CODE eq '0000123456'

I get the proper results for the same. But I want to Query using EFFECTIVE_DATE and EXPIRY_DATE. They look like:

I am not able to Query EFFECTIVE_DATE and EXPIRY_DATE.

Things I have tried are:

1. EFFECTIVE_DATE eq datetime '2014-11-30T00:00:00'

2. EFFECTIVE_DATE eq datetime '2014-11-30T18:30:00'

3. EFFECTIVE_DATE eq DateTime '2014-11-30T00:00:00'

4. EFFECTIVE_DATE eq '2014-11-30T00:00:00'

5. EFFECTIVE_DATE eq datetime ('2014-11-30T00:00:00')

6. EFFECTIVE_DATE eq datetime '1-NOV-2014'

But each time I get error as : Invalid filter expression or ORA-01861: literal does not match format string

Can anybody help Me in Querying the Date ?

[Former Member]

Hi Nikhil,

$filter= DEALER_CODE eq '0000123456' & EFFECTIVE_DATE eq datetime'2014-11-30T00:00:00' & EXPIRY_DATE eq datetime'2014-12-30T00:00:00'

Above query should work fine. This is how i query and that works for me.

if this didnt work then let me know.

can you tell me how you are querying for dates, i mean your URI.

Best Regards,

Umakant Jamdade

[Former Member]

Hi Umakant,

I tried as you suggested. But I get the following error:

[KRS_SW]

Thank you Umakant ...

[marvin_hoffmann]

Hi Nikhil,

because I was running in the same problem some weeks ago with a customer, here an explanation.

The error you are seeing is thrown by the underlying Oracle database, because an attribute (in your case the date) is not properly formatted. The IGW automatic mapping processor cannot always map/convert any given value to an equivalent database data type.

I experienced the following with IGW, Oracle DB and Dates:

• Oracle's DATE to Edm.DateTime automatic conversion is working. So you should be able to see the dates which are getting returned.
• Edm.DateTime to Oracle's DATE automatic conversion is often failing, because the Oracle DB expects the time format in a different format than produced by IGW. That means that Create and Update operations as well as filter queries with date input will not work out of the box.

So you need to use Custom Coding (JavaScript/Groovy) to manipulate the sql query in a way that the Oracle db will accept it. You could e.g. parse the whole sql query created by IGW and replace all dates with Oracle's to_date(date_here) function...

Regards

Marvin

[Former Member]

can you give an example for the same?

i.e. how can I convert Oracle's DateTime to Edm.DateTime?

OR

What should be the contents for SQL Request in the javascript?

[Former Member]

Hi

I have modified the processRequestData(message)

It has following script apart from import scripts:

	var tableMap = new HashMap();
	tableMap.put("V_SCHEMES_PORTAL","V_SCHEMES_PORTAL");
	//Create the entity map and the two property maps
	var entitiesMap = new HashMap();
	var schemesMap = new HashMap();
	//Property map
	schemesMap.put("DEALER_CODE","DEALER_CODE");
	schemesMap.put("SCHEME_ID","SCHEME_ID");
	schemesMap.put("CN_AMOUNT","CN_AMOUNT");
	schemesMap.put("SAP_CN_NO","SAP_CN_NO");
	schemesMap.put("EFFECTIVE_DATE","EFFECTIVE_DATE");
	schemesMap.put("EXPIRY_DATE","EXPIRY_DATE");
	//Entities map populated with property maps against relevant EntitySets
	entitiesMap.put("V_SCHEMES_PORTAL", schemesMap);
	//Set the header in the message object with the two maps created
	//message.setHeader(ODataCamelExchangeHeaders.JDBC_TABLE_MAPPING.toString(), tableMap);
	//message.setHeader(ODataCamelExchangeHeaders.JDBC_PROP_MAPPING.toString(), entitiesMap);
	message.setHeader("JDBCTableNameMapping", tableMap);
	message.setHeader("JDBCPropertyNameMaping", entitiesMap);
	//Logger
	log.logErrors(LogMessage.TechnicalError, "This is first log");

//-------------------End Sample Implementation---------------------------------------

return message;

Still I am getting error as

ORA-01861: literal does not match format string

what needs to be done in this case?

[rakshit_doshi]

Hi Nikhil,

Try doing something like this EFFECTIVE_DATE eq DATETIME'2014-11-30T00:00:00'.

Mind the spaces. There is no space and parantheses and from the examples you showed above it seems you are inserting spaces after DATETIME.

Hope this helps.

Thanks,

Rakshit Doshi

[Former Member]

Hi Rakshit,

I have tried with and without spaces and also with datetime, DateTime, DATETIME

[rakshit_doshi]

Is it possible for you to share the entity model schema screenshot on which you are trying to do this filter.

Also what is your backend system?

Thanks,

Rakshit Doshi

[Former Member]

<?xml version="1.0" encoding="UTF-8"?><edmx:Edmx xmlns:edmx="http://schemas.microsoft.com/ado/2007/06/edmx" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" Version="1.0">

    <edmx:DataServices m:DataServiceVersion="2.0">

        <Schema xmlns="http://schemas.microsoft.com/ado/2008/09/edm" xmlns:sap="http://www.sap.com/Protocols/SAPData" Namespace="schemesModel">

            <EntityType Name="V_SCHEMES_PORTAL">

                <Key>

                    <PropertyRef Name="SCHEME_ID"/>

                </Key>

                <Property Name="SCHEME_ID" Nullable="false" Type="Edm.String"/>

                <Property Name="DEALER_CODE" Type="Edm.String"/>

                <Property Name="CN_AMOUNT" Type="Edm.String"/>

                <Property Name="SAP_CN_NO" Type="Edm.String"/>

                <Property Name="EFFECTIVE_DATE" Type="Edm.DateTime"/>

                <Property Name="EXPIRY_DATE" Type="Edm.DateTime"/>

            </EntityType>

            <EntityContainer Name="default" m:IsDefaultEntityContainer="true">

                <EntitySet EntityType="schemesModel.V_SCHEMES_PORTAL" Name="V_SCHEMES_PORTAL"/>

            </EntityContainer>

        </Schema>

    </edmx:DataServices>

</edmx:Edmx>