Thursday, February 5, 2009

Query with DateTime field in Salesforce

If you have to work with DateTime field in Salesforce, note these differences:

1. Don't use quote in DateTime value, it's different with SQL Server or Oracle
For example:
SELECT Id FROM Account WHERE CreatedDate > 2005-10-08
In SQL Server, you must use:
SELECT Id FROM Account WHERE CreatedDate > '2005-10-08'

2. Use DateTime literals: YESTERDAY, TODAY, TOMORROW, LAST_WEEK, THIS_WEEK, NEXT_WEEK, etc.
For example:
SELECT Id FROM Account WHERE CreatedDate = YESTERDAY
In SQL Server, you may use:
SELECT Id FROM Account WHERE DateDiff(d, CreatedDate, GETDATE()) = 1

3. DateTime in Salesforce is UTC format, therefore you have to convert your datetime value to UTC format in your query which has comparision between date
For example:
In C# code, you have a control DateTimePicker dtpDateTime, you want to build a dynamic query based on this field. In this case, you can convert like that:
string strSQL = "SELECT Id FROM Account WHERE CreatedDate > " + dtpDateTime.value.ToString("yyyy-MM-ddThh:mm:ss.sssZ");


For more information, check here

No comments:

Post a Comment