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

Highlighted
Six Stars

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

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?

 


Accepted Solutions
Forteen Stars

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

@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

 

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.

All Replies
Employee

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

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

Six Stars

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

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?

Forteen Stars

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

@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

 

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Employee

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

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.

image.png

 

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

2019 GARNER 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

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog