Showing results for 
Search instead for 
Do you mean 
Community Home Request Access Read Blogs Share Your Ideas Search Community View My Settings
Reply
New Member
timothywelch
Posts: 13
Country: United Kingdom
Message 1 of 7 (2,513 Views)

Incorrect Results when using a Query on "Date Field" and "Month Equals"

I'm using ACT! by Sage Premium 2009 (11.0) Version 11.1.183.0 and I've done this query:

 

 

query_error 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

...and you can see although I'm only looking for anything where the date has "06" as the month, it is bringing up other records as well (they appear to be dates where the days are 01 of the next month).

 

Can you please confirm this is a bug and/or advise of a solution.

Platinum Elite Contributor
Roy_Laudenslage
Posts: 5,758
Country: United States
Message 2 of 7 (2,487 Views)

Re: Incorrect Results when using a Query on "Date Field" and "Month Equals"

Good catch. It's related to dates that are on the first or last day of the month. All dates in SQL have a time components and that appears to be throwing the query off. I've separately reported it as a bug.
Roy Laudenslager
ACT! Certified Consultant
ACT! Report Expert
Tech Benders
royel@techbenders.com
New Member
timothywelch
Posts: 13
Country: United Kingdom
Message 3 of 7 (2,477 Views)

Re: Incorrect Results when using a Query on "Date Field" and "Month Equals"

Thanks, but I'm really disappointed. We used to use Act 2000 and it worked perfectly for us, but since now using Act 2009, we've found at least half a dozen bugs in what I would call fairly basic functionality. It's a real shame, especially as fixes are so slow in coming. :smileysad:
Moderator
dlunceford
Posts: 4,385
Country: USA
Message 4 of 7 (2,471 Views)

Re: Incorrect Results when using a Query on "Date Field" and "Month Equals"

I am unable to reproduce in ACT! 2009 Premium 11.1.  I'm using the same dates in the screenshot.

 

Platinum Elite Contributor
Roy_Laudenslage
Posts: 5,758
Country: United States
Message 5 of 7 (2,466 Views)

Re: Incorrect Results when using a Query on "Date Field" and "Month Equals"

David

Check my other post to this thread. I was able to reproduce the problem. This is an ongoing problem with date fields in the ACT! program. All date fields have a time component in the SQL database and if it's a date field that does not use the time component, that time component is set to midnight. In addition the dates are time zone sensitive as an offset from Greenwich, accessing the date field for queries from a different time zone or influenced by daylight savings time, you can get these kind of one day date errors. My fix would be to set the default time to noon rather than midnight to at least minimize the problem. I have separately reported this as a bug on the ACC newsgroup.

Roy Laudenslager
ACT! Certified Consultant
ACT! Report Expert
Tech Benders
royel@techbenders.com
Moderator
dlunceford
Posts: 4,385
Country: USA
Message 6 of 7 (2,459 Views)

Re: Incorrect Results when using a Query on "Date Field" and "Month Equals"

I haven't been able to reproduce in house on any workstations or images.  After speaking with another agent, we found an existing reported issue for the UK locale that will be resolved in a future version.

Platinum Elite Contributor
Roy_Laudenslage
Posts: 5,758
Country: United States
Message 7 of 7 (2,457 Views)

Re: Incorrect Results when using a Query on "Date Field" and "Month Equals"

David

I'm sending you a private message 

Roy Laudenslager
ACT! Certified Consultant
ACT! Report Expert
Tech Benders
royel@techbenders.com