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
irfan azizirfan aziz 

How to filter a datetime column based on time?

The below select with condition createddate=last_n_days:1 filters last one day data. But I am expecting that we ll need to query multiple times in a day. Is there function that i can use to filter based on hours(like get the date for the last 3 hours or 1 hour).
SELECT AccountId,Amount,CampaignId,CloseDate,CreatedById,CreatedDate,Description,Fiscal,FiscalQuarter,FiscalYear,ForecastCategory,ForecastCategoryName,\
HasOpportunityLineItem,Id,InCurrentHalfYear__c,IsClosed,IsDeleted,IsWon,LastActivityDate,LastModifiedById,LastModifiedDate\
FROM Opportunity where createddate=last_n_days:1
 
Shivdeep KumarShivdeep Kumar
Hi Irfan,

Please try this,

DateTime timeNow = System.now(); // Get the time now
DateTime X30MinutesAgo = timeNow.addMinutes(-30); // Subtract 30 minutes from it
System.DEBUG([SELECT ID FROM Account WHERE createdDate <:timeNow AND createdDate >:X30MinutesAgo]);
It will return you records cfreated within 30 minutes.

// OR

Select a.Id From Account a Where a.CreatedDate = TODAY AND HOUR_IN_DAY(a.CreatedDate) > 1
It will return the data which is created within 1 hr.

Please let me know if this help.

Thanks
Shivdeep
irfan azizirfan aziz
Thank you Shivdeep, i used the second option and yes the below gives all the rows modified in the last 4 days plus today date and modified time before 7:00. One thing that is different from normal SQL is that i would expect last 4 days only ie.(20,21,22,23) data. But i get for below query(20,21,22,23,24 dates data). Rest looks good.
Select a.Id,a.createddate,a.lastmodifieddate From opportunity a Where a.lastmodifiedDate = LAST_N_DAYS:4 AND HOUR_IN_DAY(a.lastmodifieddate) <7 order by a.createddate
Shivdeep KumarShivdeep Kumar
Hi Irfan,

Add one more AND which is like a.lastmodifieddate <> TODAY.

Thanks !