+ Start a Discussion
madhu_ramadhu_ra 

SOQL CALENDAR_MONTH() and WEEK_IN_MONTH() Confusion

I have a SOQL filter on a DateTime field as below
SELECT fieldList FROM myObj WHERE CALENDAR_MONTH(myDateTimeField)=12 AND WEEK_IN_MONTH(myDateTimeField) IN : (4,5) AND myDateTimeField = THIS_YEAR
This returns results for the myDateTimeField = 2014-01-01 00:00 as well.
SELECT fieldList FROM myObj WHERE CALENDAR_MONTH(myDateTimeField)=2 AND WEEK_IN_MONTH(myDateTimeField) IN : (4,5) AND myDateTimeField = THIS_YEAR
This returns results for myDateTimeField= 2014-03-01 00:00 as well.

How or why is this happening? According to the documentation WEEK_IN_MONTH(DatetTime) considers 1st day through 7th day of the month as the first week. Also CALENAR_MONTH(DateTime) should returns 1 for Jan, 4 for April etc.

But what is happening there in my case? Any thoughts?
Best Answer chosen by madhu_ra
madhu_ramadhu_ra
You are correct Sure@Dream. This has happend due to timezone difference as far as I understood. It's like salesforce converts the datetime fields into the ogranization timezone when it rendering on a visualforce page. Based on this assumption I tested number of scenarios and still the assumption is valid.

e.g. I have a record which has the value 2014-02-28 20:00 when querying from Developer Console. This gives me the value as exact field value + 11 hours when rendering on UI(which is the timezone shift of my organization). So we need to take care about filters when using datetime fields. convertTimeZone() (http://www.salesforce.com/us/developer/docs/soql_sosl/Content/sforce_api_calls_soql_select_convert_time_zone.htm) also may help here.

Hope this helps some one.

All Answers

Sure@DreamSure@Dream
Hi Madhu,

It is something related to locale i think.


Thanks
madhu_ramadhu_ra
You are correct Sure@Dream. This has happend due to timezone difference as far as I understood. It's like salesforce converts the datetime fields into the ogranization timezone when it rendering on a visualforce page. Based on this assumption I tested number of scenarios and still the assumption is valid.

e.g. I have a record which has the value 2014-02-28 20:00 when querying from Developer Console. This gives me the value as exact field value + 11 hours when rendering on UI(which is the timezone shift of my organization). So we need to take care about filters when using datetime fields. convertTimeZone() (http://www.salesforce.com/us/developer/docs/soql_sosl/Content/sforce_api_calls_soql_select_convert_time_zone.htm) also may help here.

Hope this helps some one.
This was selected as the best answer