9

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..!)

  • 1
    You could have a trigger or workflow rule which populates the time in a hidden field using datetime methods / formula functions. Use that field in your query. All stored times are GMT – techtrekker Apr 17 '13 at 7:07
8

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!

  • WOW!! This is something very cool. From where did you get this, because the date functions that you have pointed to, dont appear to be in the Apex Developer guide. – Shailesh Deshpande Apr 17 '13 at 7:39
  • 2
    @ShaileshDeshpande It's in "SOAP API Developer's Guide" (you can click the top link on the page). I guess the reason it's in external document is because SOQL / SOSL can be run externally (SOAP API, REST API), not only within Apex. But I know what you mean... Similarly I wish some info from Visualforce docs would be duplicated in Apex docs (StandardSetController for example). – eyescream Apr 17 '13 at 8:13
  • Thanks eyescream for the reply, But I have to compare whole time not just hours. because time can also be like 2:30 to 4:30. – Ravi Krishan Apr 18 '13 at 8:23
2

I am not sure if the "bookingDate" in your system is populated manually or via some logic. If it is done manually, then one way to do this would be to create a Number field on the object say "bookingTime(2,2)" so that the user could enter the time manually. Then later in your query you could do something like:

Double minTime = 2.00;

Double maxTime = 4.00;

List<YourObject> yourObjectRecords = [Select all the fields that you want from YourObject where bookingTime__c >=: minTime and bookingTime__c <=: maxTime]

If the "bookingDate" in your system is populated some logic you might have to create a trigger as suggested by @techTrekker.

  • Yes, it is populated manually, So this should be the solution. – Ravi Krishan Apr 18 '13 at 8:44
  • If you do the above, be aware that there is no Apex "Time" Object method other than "System Time" in SF which won't apply to what's being suggested. Based on experience, I highly recommend you use a drop-down to implement this type of feature that includes AM/PM or uses a 24 hour clock time. If you don't, at least make certain you use a validation rule of some kind. Otherwise you may encounter problems when you attempt to calculate the difference between start and stop times (or min and max times) to get the "actual time". – crmprogdev Apr 24 '13 at 13:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.