topic Re: How to use Talend Datetime function in SOQL Query (tSalesforceinput) in Talend Studio

How to use Talend Datetime function in SOQL Query (tSalesforceinput)

rohitpatil1993 — Sat, 16 Nov 2024 02:55:03 GMT

Hello
I have a query which should fetch results from salesforce on the basis of below WHERE clause :

Select xyz
FROM xyz

WHERE StartDateTime >= TODAY AND StartDateTime <= NEXT_N_DAYS:3

Note: For (TODAY) I suppose I will have to use getDate() & for NEXT_N_DAYS:3 I will have to use addDate() and pass them at runtime.

My question is how do I use the this functions in my SOQL Query to fetch desired output. I tried using global variables and context variables but faced different technical issues related to type conversion.

Now, I am unsure of the approach I will have to use. Can anyone confirm the ideal way of doing this task. In case I have a confirmed approach, I will stick to it and try to resolve the technical challenges.

Please let me know If the question is unclear.

Re: How to use Talend Datetime function in SOQL Query (tSalesforceinput)

TRF — Sun, 22 Mar 2020 21:26:09 GMT

The best and simplest solution is to use standard salesforce literals such as TODAY, NEXT_N_DAYS or others as soon as the condition is always the same. The example from your request should work.
If you want a dynamic condition, you need to construct it as a string by concatenate different parts like this (as an example):
"StartDateTime >= " + (Sting)globalMap.get("minDate") + " AND StartDateTime <= " + (Sting)globalMap.get("maxDate")
Here minDate and maxDate variables has been caculated before the tSalesforceInput component using appropriate Talend date functions such as:
TalendDate.getDate("yyyy-MM-dd")
or
TalendDate.parseDate("yyyy-MM-dd", TalendDate.addDate(TalendDate.getDate("yyyy-MM-dd"), "yyyy-MM-dd", 3, "dd"))

Re: How to use Talend Datetime function in SOQL Query (tSalesforceinput)

rohitpatil1993 — Mon, 23 Mar 2020 05:57:06 GMT

Thanks @TRF
I understood the second part of your reply. However as stated
"The best and simplest solution is to use standard salesforce literals such as TODAY, NEXT_N_DAYS or others as soon as the condition is always the same."
Are you saying that if If I run the below query in tSalesforceinput, It will work in Talend?
Please excuse if it is a silly question since I am not very familiar with SOQL.

SELECT

Event.id,

Event.Account.Name,

Event.Account.telephoneMobile__c

FROM Event

WHERE StartDateTime >= TODAY AND StartDateTime <= NEXT_N_DAYS:3

AND codeMagasin__c in ('0062', '0374', '0368', '0041')

Re: How to use Talend Datetime function in SOQL Query (tSalesforceinput)

TRF — Mon, 23 Mar 2020 06:06:30 GMT

Yes it will work

Re: How to use Talend Datetime function in SOQL Query (tSalesforceinput)

TRF — Mon, 23 Mar 2020 11:25:59 GMT

@rohitpatil1993 did you try? does it works as expected?

If so, thanks to mark your case as solved.

Re: How to use Talend Datetime function in SOQL Query (tSalesforceinput)

rohitpatil1993 — Thu, 26 Mar 2020 07:43:53 GMT

Thank you very much @TRF
It worked when I used the SOQL as it is in tSalesforceinput.