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

Highlighted
Six Stars

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

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.  


Accepted Solutions
Sixteen Stars
Sixteen Stars

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

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"))

TRF

View solution in original post


All Replies
Sixteen Stars
Sixteen Stars

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

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"))

TRF

View solution in original post

Highlighted
Six Stars

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

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')

Highlighted
Sixteen Stars
Sixteen Stars

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

Yes it will work

TRF
Highlighted
Sixteen Stars
Sixteen Stars

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

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

If so, thanks to mark your case as solved.


TRF
Highlighted
Six Stars

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

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

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 3

Read about some useful Context Variable ideas

Blog