I am facing issue with Datetime column in WHERE clause. Below two queries are giving different counts.

SELECT COUNT(CallTime_c) FROM SF...TableName WHERE CallDateTime > '09-13-2018 09:01:00' and CallDateTime < '09-13-2018 09:05:00'

SELECT * FROM OPENQUERY(SF,'SELECT COUNT(CallTime_c) FROM TableName WHERE CallDateTime > 2018-09-13T09:01:00Z AND CallDateTime < 2018-09-13T09:05:00Z')
  • 3
    Are you using a third party tool to run these queries, or perhaps working in Marketing Cloud? The first query you've provided isn't valid SOQL (the query language supported by Apex/Sales Cloud/etc...). – Derek F Sep 14 '18 at 16:06

This strikes me as expected behavior rather than an issue.

The ISO 8601 datetime format 2018-09-13T09:01:00Z breaks down like this:

  • Year: 2018
  • Month: 9
  • Day: 13
  • Hour: 9
  • Minute: 1
  • Second: 0
  • Time zone: Z (i.e. "Zulu" time, UTC+0)

The time zone is the key here. When you specify the datetime in the other format (From what I know, DBAmp uses T-SQL) you've listed 09-13-2018 09:01:00, the timezone is not present, and the DBAmp documentation makes it sound like it assumes you're specifying local times by default.

If you're not in the UTC+0 time zone, then it's not at all surprising that those two different queries would return different results, because you are actually querying for records that fall under different datetime ranges

Probably the easiest solution here is to adjust your "openquery" SOQL query to specify your timezone. If I were doing this, being in Minnesota, USA where we're currently UTC-5, the datetime string I'd be using is "2018-09-13T09:01:00-0500"

Adjust according to your own UTC timezone

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.