[SalesForce] how to compare time only from Datetime field in SOQL

I have a custom Object which contains a Datetime field let say bookingDate
It contains date and time of booking.

now i need to get all bookings starts from 2:00 PM to 4:00 PM regardless of date value. for current month.

I couldn't find any function/ query to get all rows which matches the criteria.
Any ideas how to do that..?
(If not then i will have to get all rows that comes under the month and filter data using logic. But that may not be efficient. as it will be fetching so many unnecessary records..!)

Best Answer

Check the list of date functions and date literals. Something like this?

SELECT Id, Name, SystemModstamp
FROM Account
WHERE SystemModStamp = THIS_MONTH 
    AND HOUR_IN_DAY(SystemModstamp) >= 14 
    AND HOUR_IN_DAY(SystemModstamp) <= 16

EDIT

Ravi pointed out that this will cover too much time (2 PM till 4:59:59 PM).

So there would be couple of ways to deal with it:

SELECT Id, Name, SystemModstamp
FROM Account
WHERE 
    (SystemModStamp = THIS_MONTH AND HOUR_IN_DAY(SystemModstamp) >= 14 AND HOUR_IN_DAY(SystemModstamp) <= 15)
    OR 
    SystemModStamp IN (2013-04-01T16:00:00Z, 2013-04-02T16:00:00Z, 2013-04-03T16:00:00Z, 
    2013-04-04T16:00:00Z, 2013-04-05T16:00:00Z, 2013-04-06T16:00:00Z, 2013-04-07T16:00:00Z)

Bit ugly but first part deals with 2:00 till 3:59 and then we ask about exact matches. You could really generate such long query or use a binding to List<DateTime> variable. Create one correct date & time and then keep calling addDays() on it for example.

Another way would be to accept it as is and then filter the times past 16:00:00 manually in the code? Not ideal but it'd still be better than fetching whole month's worth of bookings!

Update

Borrowing from @highfive's answer, the easiest way to do this is use the DAY_ONLY operator to convert the DateTime.

SELECT Id FROM Case WHERE DAY_ONLY(ClosedDate) >= :startOfMonth
  AND DAY_ONLY(ClosedDate) <= :endOfMonth

Old Answer

So it seems like there are four options. I'd probably go for the last one, which seems the clearest and simplest. In hindsight this question appears subjective and probably not right for the forum =.

First, you can use @sathya's suggestion which works but might be counter-intuitive if you don't understand the date to datetime conversion that's happening.

Date startOfMonth = Date.newInstance(2012,9,1);
Date endOfMonth = Date.newInstance(2012,10,1);
List<Case> cases = [
        select id from Case 
        where closedDate >= :startOfMonth 
        and closedDate <= :endOfMonth];

Second, you can exactly specify the date time window. Which works but feels a little verbose.

DateTime startOfMonthDT = DateTime.newInstance(2012,9,1,0,0,0);
DateTime endOfMonthDT = DateTime.newInstance(2012,9,30,23,59,59);
List<Case> cases = [
        select id from Case 
        where closedDate >= :startOfMonthDT 
        and closedDate <= :endOfMonthDT];

Third, if you're only worrying about the current or last month you can use date literals.

List<Case> cases = [
        select id from Case 
        where closedDate = LAST_MONTH];

Finally, you can use the date functions, which seems like the clearest (to me at least)

Integer month = 9;
Integer year = 2012;
List<Case> cases = [
        select id from Case 
        where CALENDAR_MONTH(closedDate) = :month
        and CALENDAR_YEAR(closedDate) = :year];

[SalesForce] SOQL Query to count only Accounts with Opps in both this year and last year

Question 1:

You can just adjust your Where to clause to limit the result to only accounts that have children that match your criteria like this.

Accounts This Year

SELECT ID, 
      (SELECT StageName, Effective_Date__c, Expiration_Date__c 
       FROM Opportunities
       WHERE StageName='Closed Won' AND Effective_Date__c < TODAY AND Expiration_Date__c > TODAY)
FROM Account where Id In (Select AccountId From Opportunity WHERE StageName='Closed Won' AND Effective_Date__c < TODAY AND Expiration_Date__c > TODAY)

Accounts Last year

SELECT ID, 
      (SELECT StageName, Effective_Date__c, Expiration_Date__c 
       FROM Opportunities
       WHERE StageName='Closed Won' AND Effective_Date__c < TODAY AND Expiration_Date__c > TODAY)
FROM Account where Id In (Select AccountId From Opportunity Where StageName = 'Closed AND Expiration_Date__c < TODAY AND Expiration_Date__c > LAST_365_DAYS)

Question 2

You can get the count like this

Accounts This Year

SELECT count(Id) mycount
    FROM Account where Id In (Select AccountId From Opportunity WHERE StageName='Closed Won' AND Effective_Date__c < TODAY AND Expiration_Date__c > TODAY)

Accounts Last Year

SELECT count(Id) mycount
        FROM Account where Id In (Select AccountId From Opportunity Where StageName = 'Closed AND Expiration_Date__c < TODAY AND Expiration_Date__c > LAST_365_DAYS)

Question 3

You can use the same method, just combine the Where clauses from question 1

SELECT ID, 
      (SELECT StageName, Effective_Date__c, Expiration_Date__c 
       FROM Opportunities
       WHERE StageName='Closed Won' AND Effective_Date__c < TODAY AND Expiration_Date__c > TODAY)
FROM Account where Id In (Select AccountId From Opportunity WHERE StageName='Closed Won' AND Effective_Date__c < TODAY AND Expiration_Date__c > TODAY)
And Id In (Select AccountId From Opportunity Where StageName = 'Closed AND Expiration_Date__c < TODAY AND Expiration_Date__c > LAST_365_DAYS)

Best Answer

Related Solutions

[SalesForce] Using a date for a datetime field in a SOQL Query criteria

Update

Old Answer

[SalesForce] SOQL Query to count only Accounts with Opps in both this year and last year

Related Topic