+ Start a Discussion
JTecJTec 

Queries with date field

Hi,

We are trying to do queries filtering by a date field, the query is something like this:

"Select count() From Lead l where l.Status = 'Qualified' and LeadSource in ('Marketing','Database','Research') and OwnerId in ('00520000000kwBIAAY') and ConvertedDate >= 2008-01-01 and ConvertedDate <= 2008-01-31"

But this query don't count field with ConvertedDate = 2008-01-31, is this a error? It is correcly? we are doing tests and we have saw that don't filter registers when we filter for ConvertedDate = "some value"


???????


Thanks
werewolfwerewolf
Maybe it's a time zone question?  I mean, not all times that are in the day "1/31/2008" in Pacific time are in that same day in GMT.  Since you're not specifying a time zone in there, if you're querying against a datetime field, it may be implicitly assuming that the time zone is GMT, which may be wrong for where you are.
JTecJTec
Ok, but how can I say the timezone if the field is a Date field and not DateTime??

Thanks
werewolfwerewolf
Well, fair enough.  I was just looking for a possible explanation.  Does it return the correct results if you say <2008-2-1 instead of <=2008-1-31?
JTecJTec
Yes it is. It returns the correct results if we say <2008-2-1 instead of <=2008-1-31, this is the modification that we have done in our sControl.

But what can we do if we want to filter for = 2008-2-1? Have we to say <2008-2-2 and >2008-1-31???

This is not the correct form to work with dates.
werewolfwerewolf
Now here's an oddity.  I tried this myself on Lead.LastActivityDate and <= worked fine.  But when I tried it on ConvertedDate I got the same issue you got.  Wtf?  Did you file a case with Salesforce Support on this?
JTecJTec
Thanks,

I will open the case and if sfdc tell me anything I will update this post.