11-17-2011 04:04 PM
I'm writing a reporting application that retrieves history data from ACT using a vb.net OleDbConnection.
When I try to query history records, I'm getting results outside the datetimes I'm trying to select.
To troublshoot this I created 435 'Call Completed' history entries for My Record
11/14/2011 11:50:01 PM
11/15/2011 12:00:01 AM
11/15/2011 12:10:01 AM
11/17/2011 11:50:01 PM
11/18/2011 12:00:01 AM
So for 3 days, there's a history every 10 minutes. I'm trying to pull everything that recorded on 11/16/2011
I then run the following:
SELECT contact.company,history.history_type,history.end_date_time,history.record_manager FROM history,contact_history,contact WHERE contact_history.historyid = history.historyid AND contact_history.contactid = contact.contactid AND history.end_date_time > '11/16/2011 00:00:01' AND history.end_date_time < '11/16/2011 23:59:59' AND history.history_type = 'Call Completed'
I get the correct number of results for a 24 hour period(144 records) however the results I get start at 11/15/2011 5:10:00 PM & end at 11/16/2011 4:50:00 PM
They seem to be offset 7 hours.
If I add 7 hours to the select statement I get the data I'm after:
SELECT contact.company,history.history_type,history.end_date_time,history.record_manager FROM history,contact_history,contact WHERE contact_history.historyid = history.historyid AND contact_history.contactid = contact.contactid AND history.end_date_time > '11/16/2011 7:00:00' AND history.end_date_time < '11/17/2011 7:00:00' AND history.history_type = 'Call Completed'
Why is everything 7 hours offsett?
Can anyone help me understand what's going on?
11-17-2011 05:24 PM
11-17-2011 09:34 PM
I'm in USA Mountain Standard Time which is GMT -7.
Oh my, that can't be coincidence.
Does it evaluate SELECT Queries based on GMT?
This application will access databases all over the US & Canada, lot's of different time zones, yikes
11-17-2011 10:42 PM
11-18-2011 05:32 AM
It's very kind of you to help, thankyou. The results I'm getting make perfect sense now.
I'm retriving the data via the SDK, using a .pad, username & password then the connection string so I never see the 'Data Link Properties Advanced Tab'
The data I get back from the sql select are all returned in times zones local to the databases(which is what I want.)
How could I retrieve the timezone offsett from the database via code?
I could then use that to compose the queries correctly in Zulu Time
11-18-2011 05:34 AM
11-18-2011 05:41 AM
I'm using OleDb via the SDK:
myActFramework.LogOn(ACTPadFile, uName, uPass)
dbConectString = myActFramework.CurrentACTOLEDB2
Dim strSQL As String
Dim dbConnection As New OleDbConnection(dbConectString)
11-18-2011 05:48 AM