function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
divya k 20divya k 20 

extract seconds from lastupdateddate

I'm trying to get seconds from the lastupdateddate so that in my SOQLwhere condition i could check if the seconds of lastupdateddate is between 10 to 20 seconds. Googling so far, I found substr function function isn't available in SOQL and although there's date functions, there isn't one to extract upto seconds. Any help is much appreciated..
Alain CabonAlain Cabon
Hi,

The only alternative is a new formula field (type: Number) (it is heavily used with SOQL for many workarounds).

LastSeconds__c = VALUE( MID( TEXT( LastModifiedDate ), 18, 2 ) )

select LastSeconds__c , LastModifiedDate  from myobject__c where LastSeconds__c >= 10 and LastSeconds__c  <= 20

That works but the performance will not be excellent (possible timeout).
divya k 20divya k 20
Thanks for the reply Alain. I'm trying this in workbench but keep getting error "MALFORMED_QUERY: Invalid aggregate function:". Please note I'm a begginer with SOQL queries. The error could be very common in SOQL but I couldn't figure out yet why I keep getting this error for any functions that I'm trying in the select query. Wondering if you would have the answer..thanks
Alain CabonAlain Cabon
I tested the formula myself as well as the SOQL request. That works for me (sure).

Could you post your field formula and your request?

 
divya k 20divya k 20
It must be me doing something silly being unfamiliar with SOQL. 

Here's one query:

SELECT AccountUUID__c FROM Account WHERE VALUE(MID(TEXT(LastModifiedDate),18,2))=10
Error: 
MALFORMED_QUERY: 
FROM Account WHERE VALUE(MID(TEXT(LastModifiedDate),18,2))=10
^
ERROR at Row:1:Column:55
expecting a right parentheses, found '('

Other Query:

SELECT TEXT(LastModifiedDate) FROM Account

Error:

MALFORMED_QUERY: Invalid aggregate function: TEXT

 
Alain CabonAlain Cabon
Ok, that is not correct.

You need a new custom field in Account

User-added image

User-added image


User-added image

User-added image



User-added image



You need a new custom field lastseconds__c ( formula number )

 
Alain CabonAlain Cabon

User-added image

That works fine but the performance are not excellent with this simple workaround

You need a new custom field lastseconds__c ( formula number )
 
divya k 20divya k 20
appreciate ur time alain but i needed to use this in Informatica to extract from SFDC so i won't be able to use custom field in that case
Alain CabonAlain Cabon
Hi,

I know very well Informatica ETL (I am using it very often). 

Fortunately, we do export custom fields everyday from Salesforce with Informatica (otherwise we'd be in big trouble).

You have a very limited version of Informatica or a very old obsolete version.

You should update your version of Informatica.

Read the following post:
https://developer.salesforce.com/forums/ForumsMain?id=906F00000008nhtIAA

You cannot update a formula field in Salesforce with Informatica (non sense) but you can read it (fortunately).
Alain CabonAlain Cabon
By the way, if you have the Information ETL, you can read the all thing in bulk mode and filter the data in your mapping but you seem to have a very weak version of Informatica (probably very old). 
divya k 20divya k 20
That is exactly what I'm trying to do Alain. So the query that i've shared before will need to go in Informatica source where the query will be issued to SFDC. Btw, we're using Informatica cloud latest version but we need SOQL query in Informatica source.
Alain CabonAlain Cabon
You can try to create a process or a trigger to save the value of second of the lastmodifieddate in a field type number but it is ridiculous and highly risky (loop).

How many records have you in this object? You can try my solution with a new formula field. Your object should be small.

LastModifiedDate is indexed so you can query the records updated during the last month (or last 6 months) (simple source filter and you filter outside the source filter in your mapping for the seconds).

You can request salesforce.com Customer Support to create a custom index on a deterministic formula field (like here)
https://developer.salesforce.com/blogs/engineering/2013/03/force-com-formula-fields-indexes-and-performance-gotchas.html

Non-Deterministic Formula Fields: when you are using Today() or Now() it is not the case.
peter coldpeter cold
Buy Ielts Certificate Without Exam, IELTS Preparation, Buy Ielts Certificate Without Exam in DUbai, buy IELTS Qatar..
Back to top ..

Whatsapp======= (+27)73 675 2934)

Skype name:: raul bestpro

Buy original 100% genuine registered verified Ielts Certificates Without Exam

ielts certificate for sale...
divya k 20divya k 20
Thanks for all your support Alain. We've got roughly 100M records but we might now just do a one-off load with PK chunking option from informatica to make the solution simpler.