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
American DataAmerican Data 

Querying Leads by Creation Date

Could someone please provide me with assistance with the following query question.

I would like to query a set of leads based upon the date the lead record was created.  It seems that the logical date to use is in the CreatedBy field.  However, this appears to be an aggregate field, i.e., containing two values.  Is it possible to query leads by their creation date? 

As a test I attempted to select the CreatedBy field with the following query and find what is returned:

Select CreatedBy, FirstName, LastName, Email, ID from Lead

I receive an error message which says "No such column "CreatedBy" on entity "Lead."  Since this is a system field, is it for some reason treated differently (i.e., it can't be selected?).

When this didn't work, I found that the Lead object has methods to return the createdDate and the createdID.  When I try and perform a query such as:

"Select FirstName, LastName, Email, ID from Lead"

and attempt to call lead.getCreatedDate() the return value is "null".

I'd really prefer to select based on the creation date, and query all the leads and then filter them by creation date after they are returned.  This creates a lot of unneeded overhead.

So far none of these attempts are bringing me closer to understanding how to query a lead based on the creation date.  To be honest, I didn't think this last attempt would work since CreateBy was not selected, but I figured I'd see what I could learn from trying.

Thanks in advance for anyone who can assist.

If it makes any difference, I'm using the Java API.

Regards,

Josh

SuperfellSuperfell
add the fields you want to the select list, CreatedById and CreatedDate
American DataAmerican Data

Thank you Simon.

I'll look to the WSDL to identify other fields I'm not sure about.  Is this the best place to look for fieldnames available for query for the various objects?

Additionally, CreatedDate is a DateTime type.  Must I include a Data and Time in the value I want to match, or will providing just the date return all items that were created on that day regardless of time.

Id I can query just by date, I imagine the following could work:

Select CreatedDate, FirstName, LastName, Email, ID from Lead Where CreatedDate = 2005-07-17.

I don't believe this does work.

Therefore, the option to get all leads for the particular day is:

Select CreatedDate, FirstName, LastName, Email, ID from Lead Where CreatedDate >= 2005-08-17T00:01:01 And CreatedDate <= 2005-08-17T24:59:59

Is this correct?  Also, do the comparison operators need to be escaped before performing the query or can they be sent in as above?

Thanks again.

 
SuperfellSuperfell
The WSDL or the results of the describe call list all the fields.

CreatedDate >= 2005-08-17T00:01:01 And CreatedDate
is not quite right, as you'll need to take into account Timezones

CreatedDate >= 2005-08-17T00:00:00Z And CreatedDate < 2005-08-18T00:00:00Z

which is midnight to midnight UTC (aka GMT)
American DataAmerican Data

Thanks Simon.

So, if I'm in the PST Timezon, I'll need to offset by 8 hours?
SuperfellSuperfell
yes, although you also need to take into account daylight savings time, if you're working in local time. (so, sometimes it 8 hours, sometimes its 7).