Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Hemanth.ax1701Hemanth.ax1701 

**URGENT** Querying DateTime field in SOQL dynamically

Hi,

 

I have a History Object with createdDate as DateTime data type which is in this format 2005-10-08T01:02:03Z

Now my requirement is that i need to get the current time with date in the format 2005-10-08T01:02:03Z and pass it on to the SOQL.

 

I have tried to dynamically get the current DataTime as shown below

 String str = DateTime.now().format('yyyy-MM-dd\'T\'hh:mm:ss\'z\'');

 

but the problem is that i cannot assign this string to my created datatime as shown below

SELECT Id
FROM Account
WHERE CreatedDate =: str

 

I have tried to print DateTime.now(), System.now() in the console but they all throw the output in this fashion - YYYY:MM:DD HH:mm::SS. 

 

Can i please know how do i resolve this or is there any function which can directly fetch me datetime in this format 2005-10-08T01:02:03Z.

 

Appretiate your help.

 

Thanks,

Hemanth

Bhawani SharmaBhawani Sharma
Try following:
List<Account> accounts = database.query('SELECT Id FROM Account
WHERE CreatedDate = ' + str);
Hemanth.ax1701Hemanth.ax1701

Hi Bhawani,

 

The suggested approach did not fetch any records and did not throw any exceptions even. After a small work around to your idea as shown below:


String str = DateTime.now().format('yyyy-MM-dd\'T\'hh:mm:ss\'z\''); 
String queryStr = 'SELECT Id FROM Account WHERE CreatedDate ='+str;
List<Account> current = database.query(queryStr);

 

 I got an exception - 

"Error:Apex trigger ChangeTrigger caused an unexpected exception, contact your administrator: ChangeTrigger: execution of AfterUpdate caused by: System.TypeException: Invalid date/time: 2013-04-09T10:10:27z: Trigger.ChangeTrigger: line 17, column 1"

 

 

Please correct me if Im wrong anywhere.

 

 

Thanks a lot,

Hemanth
Raj.ax1558Raj.ax1558

Hi Hemanth, 

 

When you filter records on the behalf of Createddate or modifieddate, Always remember don't write " =:  " because, If the time differnec in Seconds it will not fetch. You have used Now() means present time then how can you fetch old record. 

It is always write the query in this way - 

 

[SELECT Id FROM Account WHERE CreatedDate =>str AND CreatedDate =< str2]

 

Str & Str2 are two differnet time that want fetch record between these.

 

Click on KUDOS button if the post helps you! & marked as solution for others users help in a same query.

 

Thank You, 

Raj jha

 

 

 
Bhawani SharmaBhawani Sharma

http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_soql_select_dateformats.htm

 

SELECT Id
FROM Account
WHERE CreatedDate > 2005-10-08T01:02:03Z
Sean TanSean Tan

This worked for me for dynamic SOQL:

 

String dateTimeFormat = DateTime.now().format('yyyy-MM-dd\'T\'hh:mm:ss\'Z\'');
System.debug(Database.query('select Id from Account where CreatedDate = ' + dateTimeFormat));

 
gaisergaiser

If your source date value is in DateTime format already then you can simplify your life by using it directly like so:

 

DateTime val = DateTime.now();

System.debug(Database.query('SELECT Id FROM Account WHERE CreatedDate =: val'));

 

The important point is that val has to be within the current apex scope.
sfdcfoxsfdcfox
Note that the date/time was not formatted correctly. The correct format string is:

'yyyy-MM-dd\'T\'HH:mm:ss.SSS\'Z\''

HH is 0-23 hour, hh is 1-24 hour (incorrect). The SSS is milliseconds, and required to be correct.

However, as stated by gaiser, you can normally bind to a DateTime value directly to avoid the hassle of formatting correctly.
Hemanth.ax1701Hemanth.ax1701

Hi Guys,

Thanks for all the solutions.

 

1) As posted by Gaiser,

 

DateTime val = DateTime.now();

System.debug(Database.query('SELECT Id FROM Account WHERE CreatedDate =: val'));

 

The above solution isn't working as the val is fetching time in different format i.e., YYYY:MM:DD HH:mm::SS. But the createdDate in the history object is in 

yyyy-MM-dd\'T\'hh:mm:ss\'Z\'

2) The solution posted by Sean Tan,

 

String dateTimeFormat = DateTime.now().format('yyyy-MM-dd\'T\'hh:mm:ss\'Z\'');
System.debug(Database.query('select Id from Account where CreatedDate = ' + dateTimeFormat));

 

I get no exceptions but it returns no rows i.e 0 rows.

 

I have also put in the system debug to get the DateTime and when i use this value in developer console i get 1 row

Can somebody throw some light of what/where exactly i am doing things wrong. 

 

Please Suggest. Thank you all once again :)

 

Regards,

Hemanth

 

 
Bhawani SharmaBhawani Sharma

Hi Hemant,

 

I started again reading your problem and I am curious to know why you are converting Date Time to string. On your history object, CreateDate is DateTime type field, So you can directly use static query.

 

List<History__c> histories = [Select Id from History__c where CreateDate =: DateTime.Now()];
Hemanth.ax1701Hemanth.ax1701

Thanks Bhavani,

 

It worked! There was some problem in the logic. I corrected it and ended up another such problem.

 

I have written a trigger  on a custom object after every insert and update.

 

trigger ChangeTrigger on Country__c (after insert, after update) {
System.debug('inserted');
    DateTime dateTimeFormat = DateTime.now();
    List<Country__History> current =  [SELECT Id,CreatedById,CreatedDate,Field,NewValue,OldValue,ParentId FROM Country__History where CreatedDate=:DateTimeFormat];
     System.debug('History Size'+current.size());
}

 The above code displays 0 rows in the logs but when I execute the same query in the developer console, I found that the 1 row that is edited at that particular time is displayed in the logs.
Bhawani SharmaBhawani Sharma
Why do you want to hit the History object at the time of record creation? I don't think so if this is really required.
rajesh k 10rajesh k 10
Hi,
          I have one requirement.I have one object as Salesorder__c.In this object Expiredate field is there.I want to run batch apex as (ExpiryDate__c-Today())=90 ,create an opportunity in an opportunity object how?

Note:Opportunity is the lookup of SalesOrder__c object.

please help me............
stan_sfdcstan_sfdc
All well explained in this blogpost -
https://nextgensalesforce.wordpress.com/2015/11/30/dynamic-soql-query-results-in-local-time/