topic How to convert date to string in the SOQL query in tSalesforceinput component? in Talend Studio

How to convert date to string in the SOQL query in tSalesforceinput component?

rp2018 — Sat, 16 Nov 2024 06:02:02 GMT

We are using tSalesforceinput component and writing a following query to extract data from it.

"SELECT Name,CreatedDate,CreatedById,LastModifiedDate FROM Finance__c WHERE LastModifiedDate >= '"+context.GlobalExtDT+"'"

Context variable called GlobalExtDT is in a string format

How to convert LastModifiedDate from date type to string type in the SOQL query to compare?

Re: How to convert date to string in the SOQL query in tSalesforceinput component?

Anonymous — Mon, 22 Apr 2019 19:40:26 GMT

Hi,

Please refer below posts from Salesforce forums.

https://developer.salesforce.com/forums/?id=906F00000009QXpIAM

https://developer.salesforce.com/forums/?id=906F0000000AkNiIAK

https://developer.salesforce.com/forums/?id=906F000000096TdIAI

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: How to convert date to string in the SOQL query in tSalesforceinput component?

rp2018 — Mon, 22 Apr 2019 20:56:35 GMT

The three posts did not help me resolve my issue. Anyway to convert the string context.GlobalExtDT to the date type? This will resolve my issue when comparing in SOQL.

So far this is what I've done.

1. Created a context variable called GlobalCvtDate as Date format

2. context.GlobalExtDT = "2019-02-14 19:23:50.000"; --NOTE: This value is coming from a parent job.

3. in tJava:

context.GlobalCvtDate = TalendDate.parseDate("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'",context.GlobalExtDT);

NOTE: I'm getting Thu Feb 14 19:23:50 CST 2019 --> This value is not taken by SOQL. Any way

to get exact value "2019-02-14 19:23:50.000" as a date format?

Re: How to convert date to string in the SOQL query in tSalesforceinput component?

manodwhb — Tue, 23 Apr 2019 05:03:14 GMT

@rp2018 , I will suggest you below way.

1) In a parent job convert date to string and load into context and pass it to subjob.

2) and read the query below way

"SELECT Name,CreatedDate,CreatedById,LastModifiedDate FROM Finance__c WHERE LastModifiedDate >="+ context.GlobalExtDT

Re: How to convert date to string in the SOQL query in tSalesforceinput component?

Anonymous — Tue, 23 Apr 2019 05:49:03 GMT

Hi,

The Salesforce forums says that you need to feed the date value as string. So you will have to parse the date value in the format expected by Salesforce and the SOQL will take it from there.

If you want to format a date to String in any specific format, please use formatDate function under tMap as shown below.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂