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
RajanRajan 

SOQL query problem

Hi friends,

I have a custome object in which I am saving records and their is a field with name Date_and_Time__c which is containing time and date for next schedule. I need a query which will give all records during current time and next 5 mints.  For example if current time is 4:30 PM and I have given time 4:32 PM in Date_and_Time__c field then it should return this record and if their is any other records during this interval then it sould give all records.
My code is as below:
*****************
global static string GetReminder(){  
        
        DateTime dt = datetime.now();
        DateTime dtIST = dt.addMinutes(330); //330 is GMT +5:30 as IST conversion
        //String CurrentdateTime = dt.format('yyyy-MM-dd\'T\'hh:mm:ss\'Z\'');
        DateTime dt1 = dtIST.addMinutes(5); // 5 minutes after current time.
        
        List<SetReminder__c> reminderList = [Select Id, Name, Date_and_Time__c, Next_Best_Action__c, Recom_External_Id__c, Recommendation__c from SetReminder__c where (Date_and_Time__c >=: dtIST and Date_and_Time__c <=:dt1)];
      
        system.debug('DT:' + dtIST);
        system.debug('DT1:' +dt1);
        system.debug('ReminderList:' +reminderList);
         return null;
        }
   
Thanks in  advance
Regards:
Rajan   
Best Answer chosen by Rajan
Paul S.Paul S.
Your datetime string has to be in this format: yyyy-MM-dd HH:mm:ss
Change line 2 to: 
String CurrentdateTime = dt.format('yyyy-MM-dd HH:mm:ss');
And then remove line 4 (strDate = strDate.replace...)
 

All Answers

Paul S.Paul S.
Rajan - I think you might need to try the query without the IST conversion since Sf is also storing your datetime field as GMT. 
RajanRajan
Thanks Paul for quick response,

I tried but its not returning any record for this interval and I have updating date and time in records as per this interval only. Any idean for this?
RAHUL CHOUDHARY 11RAHUL CHOUDHARY 11
I thik the refrence object of dt have a problem its should currnt may be 
RajanRajan
My object database time which I am saving is in this format '9/19/2017 12:43 PM'. Is their any problem with this?
RajanRajan
I think their is problem with 2 dates in query. Could anyone help me to identify how to query for a particular interval of time?
I want records between varriable 'dtIST ' and 'dt1'.
Abhishek BansalAbhishek Bansal
Hi Rajan,

May be it is related to the conversion issue. You can find more details in the link given below:
https://nextgensalesforce.wordpress.com/2015/11/30/dynamic-soql-query-results-in-local-time/ (http://​https://nextgensalesforce.wordpress.com/2015/11/30/dynamic-soql-query-results-in-local-time/)

Thanks,
Abhishek Bansal.
Paul S.Paul S.
I set up a similar situation in a dev org and was able to get this to work.  Activity_Date_Time__c is a datetimefield. 
Datetime dtMin = Datetime.now();
Datetime dtMax = Datetime.now().AddMinutes(5);
Account a = [select id, activity_date_time__c from account where activity_date_time__c >= :dtMin and activity_date_time__c <= :dtMax limit 1];

 
RajanRajan
Thank you Paul,

I am doing the same but not returning any record. Is their any problem with Org Time  or time format. My Org time is ''GMT +5:30(IST)'' and time format is in AM and PM format but salesforce is returning in 24 hours format after debuging and once I m saving record then it is saving value in the field is  ''9/20/2017 5:40 AM''.
RajanRajan
As per debug log, salesforce is returning datetime in the format - 2017-09-20 09:40:38  and in query editor it is giving records if i m passing datetime as ---2017-09-20T09:09:00Z.  If i m converting like below code then its throwing error- ''Invalid date/time: 2017-09-20T03:38:53Z''
My code is
**********
 DateTime dt = datetime.now();
        String CurrentdateTime = dt.format('yyyy-MM-dd\'T\'hh:mm:ss\'Z\'');
        String strdate = CurrentdateTime;
        strDate = strDate.replace('\'T\'', ' ').replace('\'Z\'', ''); 
        datetime dtv = datetime.valueOf(strDate);
 
Paul S.Paul S.
Your datetime string has to be in this format: yyyy-MM-dd HH:mm:ss
Change line 2 to: 
String CurrentdateTime = dt.format('yyyy-MM-dd HH:mm:ss');
And then remove line 4 (strDate = strDate.replace...)
 
This was selected as the best answer
RajanRajan
Thanks Paul,
Its working fine now.
Paul S.Paul S.
That’s great. What was the issue?
RajanRajan
Datetime data formating problem was their. It was not accepting the value which I was passing because it was not converting string to datetime.
RajanRajan
Hi Paul,

I have one more requirement. Could you pls help me out for that?
I have designed a custom button for sending email. I have a custom table where I'm displaying data. Whenever I'll select any record using checkbox and click that button then that particular record should go as email with that VF template which I'm using. I'm able to send email with that template but records are not coming. I think their is some problem with data mapping with that template. I'm displaying recods in table from 3 different custom objects with Account and contact standard object. Any idea or do I need to share code with you?
Paul S.Paul S.
Yeah, can you please share the code?
RajanRajan
Could you please shere me your email id or do I have to paste all code here because code is having so many lines?
Paul S.Paul S.
pjsubbioAThotmailDOTcom
RajanRajan
Thanks Paul,
I have sent you the code. Please check and suggest me or do required changes. It will be great help for me. Thanks in advance.