+ Start a Discussion
sachin kadamsachin kadam 

SOQL to retrive all opportunities created 21 days from today

Hi All

i need SOQL to find opportunities that where created 21 days ago.
example: today date 09/30/216. i need all opportunities create on 09/09/2016.
the problem i am having is the createddate is a  datetime and i need all the records from that date no mater what time they where created.
Best Answer chosen by sachin kadam
UC InnovationUC Innovation
Hi Sachin,

Could you elaborate? I just tried on my instance and it works. It should create a new instance of datetime, set to midnight based on your local time zone. For example, if your local time zone was PST (GMT +7) then it will show 2016-09-09 07:00:00. 

If you don't want that local time zone conversion, then you could just convert the datetime to a date. You should still be allowed to make comparisons between datetime and date.

DateTime dT = System.now();
dT = dT.addDays(-21);
Date newDate = Date.newinstance(dT.year(), dT.month(), dT.day());

All Answers

UC InnovationUC Innovation
Hi Sachin,

You could do this by removing the time from the datetime, by creating a new instance of the datetime 

DateTime dT = System.now();
dT.addDays(-21);
DateTime newDate = DateTime.newinstance(dT.year(), dT.month(), dT.day());

Please select best answer if this helped!
sachin kadamsachin kadam
Hi UC Innovation

that is not working.
UC InnovationUC Innovation
Hi Sachin,

Could you elaborate? I just tried on my instance and it works. It should create a new instance of datetime, set to midnight based on your local time zone. For example, if your local time zone was PST (GMT +7) then it will show 2016-09-09 07:00:00. 

If you don't want that local time zone conversion, then you could just convert the datetime to a date. You should still be allowed to make comparisons between datetime and date.

DateTime dT = System.now();
dT = dT.addDays(-21);
Date newDate = Date.newinstance(dT.year(), dT.month(), dT.day());
This was selected as the best answer
Mahesh K 22Mahesh K 22
HI Sachin,
once try this query
select Name,StageName,CloseDate from opportunity where createddate =LAST_N_DAYS:29
sachin kadamsachin kadam
Hi Mahesh

LAST_N_DAYS will give you data from today to today - Nth date.
i dont need range, what i need is data from 21 days ago.
example : tadays date : 09/30/2016 i need data created on day 09/09/2016.
sachin kadamsachin kadam
Hi UC Innovation

your way worked but i had to modify it.

DateTime sdT21 = System.now().addDays(-21);
DateTime snewDate21 = DateTime.newinstance(sdT21.year(), sdT21.month(), sdT21.day());
DateTime edT21 = System.now().addDays(-20);
DateTime enewDate21 = DateTime.newinstance(edT21.year(), edT21.month(), edT21.day());
Select Name, createddate from opportunity where createdDate >=:snewDate21 AND createdDate <:enewDate21