function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
kailash chandra 25kailash chandra 25 

how to use date function CALENDAR_MONTH() in orderby clause in SOQL

I want to get records of contact based on Birthdate.
eg: i need to display contact records in ASC order of next 60 days. so how to use GROUP BY CALENDAR_MONTH(Birthdate) ASC .
If I use GROUP BY Birthdate ASC It will return date in ascending order.

I am trying SOQL query like this
select id, name,PhotoUrl,Birthdate from Contact WHERE Birthdate != null AND Birthdate = NEXT_N_DAYS:60 ORDER BY CALENDAR_MONTH(Birthdate) ASC, DAY_IN_MONTH(Birthdate) ASC LIMIT 5 OFFSET 0

[object Object]: Ordered field cannot be aggregated in a query that is not grouped: CALENDAR_MONTH(Birthdate)

I need this solution through SOQL without using apex
Do you have any solution to sort birthdate based on month and date then please help me
Best Answer chosen by kailash chandra 25
Priyananth RPriyananth R
Hi 
I think, your expected result provided by aggregate result. 
I provide some sample code, just try it.
for(AggregateResult aggObj :[SELECT Id,calendar_month(Birthdate) mon,DAY_IN_MONTH(Birthdate) day FROM Contact WHERE                         Birthdate != NULL group by Id,Birthdate order by calendar_month(Birthdate),DAY_IN_MONTH(Birthdate)  ASC ] ) {
 System.debug('RecordId :::'+aggObj.get('Id')+'::month:::'+aggObj.get('mon')+'::Day in Month::'+aggObj.get('day'));
}                

 

All Answers

Abhishek Raj 23Abhishek Raj 23
Hi 

If you simply do order by Birthdate ASC, you will get records according to ascending order of birthdate for next 60 days.

select id, name,PhotoUrl,Birthdate from Contact WHERE Birthdate != null AND Birthdate = NEXT_N_DAYS:60 ORDER BY Birthdate ASC LIMIT 5 OFFSET 0
kailash chandra 25kailash chandra 25
Hi,
Birthdates are like this.
5/06/1992
5/04/1991
2/27/1995
3/03/1993
3/04/1993
OUTPUT Will Like
5/04/1991
5/06/1992
3/03/1993
3/04/1993
2/27/1995

I need sorted based on Month and day
2/27/1995
3/03/1993
3/04/1993
5/04/1991
5/06/1992
I hope you understand my requiremnent.


 
Priyananth RPriyananth R
Hi 
I think, your expected result provided by aggregate result. 
I provide some sample code, just try it.
for(AggregateResult aggObj :[SELECT Id,calendar_month(Birthdate) mon,DAY_IN_MONTH(Birthdate) day FROM Contact WHERE                         Birthdate != NULL group by Id,Birthdate order by calendar_month(Birthdate),DAY_IN_MONTH(Birthdate)  ASC ] ) {
 System.debug('RecordId :::'+aggObj.get('Id')+'::month:::'+aggObj.get('mon')+'::Day in Month::'+aggObj.get('day'));
}                

 
This was selected as the best answer
kailash chandra 25kailash chandra 25
Hi Priyananth,
Thankyou for you quick and correct reply.
It's very helpfull for me.I used this query like this.
 
SELECT Id,Name,Birthdate,calendar_month(Birthdate) mon,DAY_IN_MONTH(Birthdate) day FROM Contact WHERE Birthdate != NULL AND Birthdate = NEXT_N_DAYS:30 group by Id,Birthdate,Name order by calendar_month(Birthdate),DAY_IN_MONTH(Birthdate) ASC LIMIT 5 OFFSET 0;