+ Start a Discussion
xpatxpat 

Startdatetime of event

Hello,

 

I want to count the event with startdatetime in Q1 , Q2, Q3 and Q4 of the current year.

 

If I write

 

 Public Integer geteventQ1Count(){
                return [Select Count() from event
                where AccountId= :System.currentPageReference().getParameters().get('id')and RecordType.Name like '%visit%' and Month(StartDateTime) < 3 AND StartDateTime = this_year ];
        }

Error: Compile Error: unexpected token: '(' at line 246 column 133 

 

If I put the following it works but I will need to chage the year every year and I don't want it.

 

Public Integer geteventQ1Count(){

                return [Select Count() from event 

                where AccountId= :System.currentPageReference().getParameters().get('id')and RecordType.Name like '%visit%' and StartDateTime >= 2009-01-01T00:00:00Z AND StartDateTime <= 2009-03-31T00:00:00Z ];

        }

Do you have some ideas on how to write it?

 

Thanks

 

Pat

Best Answer chosen by Admin (Salesforce Developers) 
sfdcfoxsfdcfox

In that case, use Binding variables in your query:

 

DateTime StartDate = DateTime.newInstance( ... ); DateTime EndDate = DateTime.newInstance( ... ); List<Event> events = [select id, ... from event where startdatetime >= :StartDate and startdatetime <= :EndDate];

You can calculate the date range to use and then run the query based off this calculation. newInstance expects several formats, the easiest of which is likely "year, month, day", expressed as Integer values. You could also use the various datetime manipulation functions to determine the date you'd like to use (i.e. addDays, etc). I'd recommend you look at the DateTime object in the Apex Code Developer's Guide. You could also create date/time input fields, parse the input, then run the query.

 

All Answers

sfdcfoxsfdcfox

In SOQL, you can specify specific time ranges using "Date Literals". For example:

 

 

SELECT ... FROM Event WHERE StartDateTime = THIS_QUARTER

For a full list of possible literals, check out the Web Services API Documentation.

 

xpatxpat
Thanks. But with date Literals you just can have this quarter or previous quarter or next n quarters etc....based on today date. I would like to have #event in Q1 Q2 etc.. and as you can't build a formula on event based on startdatetime I need to do a count in the controller.
sfdcfoxsfdcfox

In that case, use Binding variables in your query:

 

DateTime StartDate = DateTime.newInstance( ... ); DateTime EndDate = DateTime.newInstance( ... ); List<Event> events = [select id, ... from event where startdatetime >= :StartDate and startdatetime <= :EndDate];

You can calculate the date range to use and then run the query based off this calculation. newInstance expects several formats, the easiest of which is likely "year, month, day", expressed as Integer values. You could also use the various datetime manipulation functions to determine the date you'd like to use (i.e. addDays, etc). I'd recommend you look at the DateTime object in the Apex Code Developer's Guide. You could also create date/time input fields, parse the input, then run the query.

 

This was selected as the best answer
xpatxpat

Datetime Startdate1 = DateTime.newInstance (System.today().year(),1,1); Datetime Enddate1 = DateTime.newinstance(System.today().year(),3,31);

Datetime Startdate2 = DateTime.newInstance (System.today().year(),4,1); Datetime Enddate2 = datetime.newinstance(System.today().year(),6,30);

 

Great thanks for your solution

 

Patricia