Community
Showing results for 
Search instead for 
Do you mean 
Reply

7 hour time offset in oleDb SELECT results???

New Member
Posts: 19
Country: USA

7 hour time offset in oleDb SELECT results???

Hi There,

 

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

i.e.

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?

 

Platinum Elite Contributor
Posts: 14,384
Country: Australia

Re: 7 hour time offset in oleDb SELECT results???

New Member
Posts: 19
Country: USA

Re: 7 hour time offset in oleDb SELECT results???

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

 

Platinum Elite Contributor
Posts: 14,384
Country: Australia

Re: 7 hour time offset in oleDb SELECT results???

The data is actually stored in ACT! as Zulu time (GMT) with an offset... this is so it can handle remote users (sync or web) in different time zones.

On the Data Link Properties Advanced tab, I think you can set the TZ for OLEDB reports - instructions in the Help, under: Working With the ACT! OLE DB Report Provider

If dealing with databases in many zones and wanting to see the local times, you'll need to also get the offset, which I think is TZ_BIAS (at least in the ODBC and I assume in OLEDB)
New Member
Posts: 19
Country: USA

Re: 7 hour time offset in oleDb SELECT results???

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

Platinum Elite Contributor
Posts: 14,384
Country: Australia

Re: 7 hour time offset in oleDb SELECT results???

New Member
Posts: 19
Country: USA

Re: 7 hour time offset in oleDb SELECT results???

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)

 

Platinum Elite Contributor
Posts: 14,384
Country: Australia

Re: 7 hour time offset in oleDb SELECT results???

I haven't tried it but, for the default, try starting here: http://support.microsoft.com/kb/310083

The activity-specific offsets should be a field ... look for TZ_Bias