+ Start a Discussion
sales4cesales4ce 

SOQL Query to compare Dates

Hi,

 

I have a Custom Object Called "Visit" which has  two custom date fields , StartDate__c and EndDate__c.

 

I have a Soql Query which needs to compare whether visit record is withinthe date range.

My SOQL query returns an error saying "List has No rows of data".

 

example: i have a visit record which has a start date as 2011-05-10 and End Date as 2011-05-17.

 

Can anyone point me what i am doing wrong?

 

Apex class:

 

public class visit {
    visit__c todaysvisit;
    String displayvisit;
    Date today=Date.Today();
  
    
    Public visit(){
        
        System.Debug('Todays date is >>>'+today);
        todaysvisit=[Select Visit__c From Visit__c Where Visit_Start_Date__ >= today AND Visit_End_Date__c <= today Limit 1 ];
        displayvisit=todaysvisit.visit__c;
        
    }
    
    Public Tip__c gettodaysvisit(){
        return todaysvisit;
    }
    
    Public String getdisplayvisit(){
        return displayvisit;
    }
    
   
}

 

 

Thanks,

Sales4ce

 

CTISJH1CTISJH1
public class visit {
    Id todaysvisit;
    String displayvisit;
    Date today=Date.Today();
  
    
    Public visit(){
        
        System.Debug('Todays date is >>>'+today);
        todaysvisit = [Select Id From Visit__c Where Visit_Start_Date__c >= today AND Visit_End_Date__c <= today Limit 1 ];
        displayvisit=todaysvisit.visit__c; //I am unsure what you want this string to be? a name, date, description? If it was a record name you could try displayvisit = todaysvisit.RecordName__c
        

    }
    
    Public Tip__c gettodaysvisit(){
        return todaysvisit;
    }
    
    Public String getdisplayvisit(){
        return displayvisit;
    }
    
   
}
sales4cesales4ce

Thanks for your reply.

 

But was there anything wrong with my query as i get an exception at the query level?I am just making sure to get only the record that falls with in the Start and End Dates?

 

Any thoughts on the query?

 

Sales4ce

CTISJH1CTISJH1
todaysvisit=[Select Visit__c From Visit__c Where Visit_Start_Date__ >= today AND Visit_End_Date__c <= today Limit 1 ];


 

I am not sure that you are able to select a record using the Object API,  instead you would need to find a record based off the ID.   I am making the assumption that you don't have a record field named visit__c inside of your object named visit__c.

 

todaysvisit=[Select Id From Visit__c Where Visit_Start_Date__ >= today AND Visit_End_Date__c <= today Limit 1 ];

 

HaagenHaagen

Hi,

 

not sure if you've spotted this but try this query:

 

todaysvisit=[Select Visit__c From Visit__c Where Visit_Start_Date__ >= :today AND Visit_End_Date__c <= :today Limit 1 ];

 

I only added colon before the today in the query. 

 

Hope it helps!

 

Cheers!

 

Martin

sales4cesales4ce

Martin,

 

i am still unable to get it to work.

 

how do i write a query that checks whether the current date falls within the start and end date of my record.

 

Any ideas?

 

Thanks,

Sales4ce

HaagenHaagen

Hi sales4ce,

 

I did a quick example which work for me (not your object, but I do comparisons on dates). 

 

 

Date FirstDate = Date.newInstance(2010,3,1);
Date SecondDate = Date.newInstance(2010,3,7);
integer i = 0;
for (Account a : [SELECT Id, Name, First_Agreement_Date__c FROM Account WHERE First_Agreement_Date__c >= :FirstDate AND First_Agreement_Date__c <= :SecondDate LIMIT 100])
{
   i++;
}
System.debug('Accounts found: ' + i);
And, yes, I know this can be done via an aggregate function instead. 
If you are still stuck, my €0.02 is to use "Execute Anonymous" or the salesforce.schema in the Eclipse API to play around with building the query. 
Cheers!
Martin

 

sales4cesales4ce

Martin,

 

Thanks for the help.

I was able to figure out where i was going wrong. I wasn't comapring the dates correctly.

 

todaysvisit=[Select Visit__c From Visit__c Where Visit_Start_Date__ <= TODAY AND Visit_End_Date__c >= TODAY Limit 1 ];

Sales4ce