Lookup on Edit Date with "Today" results show previous day.

[JM]

Running Prem ACT! 18.1 Update 2 on Win-10 PC.

Surprised no one reported it on board but I called support and confirmed they know about and working on fix.

Given in USA its May 5th 2016 [5/5/2016] pacific time zone and I want to find records I touched (changed) today.

Right click on Edit Date with Equal To (=) by "Today".  My result is records with date of 4th.

Temp solution put in place of Today - 5/5/16.  

Only problem with this is you can't create a group with query of Today you would have to edit the query every day.

I could not figure out a custom query workaround?

Seem like since 2011 Post similar issue:

When lookup "Birth Date within Next 0 Days" contacts with dates for 19th Feb appears.

When lookup "Birth Date within last 0 days" contacts with dates for 19th Feb appears.

When lookup "Birth Date within last 1 days" contacts with dates for 18th and 19th Feb appears.

JM

[JM · Accepted solution]

Gary W,

I just confirmed D-04583 in V19 Update 4 that a Lookup on Edit Date by "Today" with times after 5PM is now working.

https://www.act.com/resources/downloads/act!-premium-v19-downloads
http://kb.act.com/app/answers/detail/a_id/38232
http://kb.act.com/app/answers/detail/a_id/38451

Thank you,

JM

[dshaw111]

I think this might be because the dates are universal dates which are based on the UK Greenwich Mean Time (GMT) and not the actual local time.

[Gary W]

Hi JM,

We haven't been able to replicate this issue in a EU or US install, however we're going to continue testing this over a couple of days.
I'll update again when I have more info.

[Gary W]

After retesting, we are still unable to replicate this issue. I suspect it's being caused by something particular about your environment.

I'll contact you via PM to arrange further troubleshooting.

[ch1p]

an you make sure that the server/Host PC that hold the database has the correct Date / Time / Country settings for your country.

[JM]

Gary,

I got your PM and will respond after this update of extra info to create the problem.

So this morning May 27th 6AM in Calf. (yes my clock and time zone Pacific is correct)

I updated my 1st record did lookup on Edit Date by TODAY and it showed up.

Then I changed time to 9PM changed a 2nd record did lookup only got the 1st record to show up.

Then I changed time to 6PM changed a 3rd record did lookup still got only 1st record.

Then I changed time back to 6AM changed a 4th record did lookup TODAY and now! I got Records 1st and 4th.

I could continue playing with my PC time but I think you can now figure out that the field also stores a time-stamp which Lookup is not following my Pacific time zone.

JM

[Gary W]

Thanks JM,

We'll retest this on Tuesday and follow up with you afterwards.

[Roy_Laudenslager]

All SQL date and time fields include both a date and time component. If the time isn't specified for a date field then the time defaults to 12:00:00 AM (midnight). ACT! to allow time especially to be localized stores times as GMT with the local machine's time zone offset from GMT. 

After going over your post again my guess is that you are using 12-hour indication for the system clock and the AM/PM setting is flipped. Check that.

[JM]

Roy,

Windows 10 (Control Panel\Clock, Language, and Region)

Customize Format Time Tab in Time formats shows for both Short and Long the notion as lower case "h" (12 hr) If I change it to upper case "H" they say it would be 24hr.

I could change that to run more tests but I will not work in 24hr format so if that is it ACT! needs to address it.

JM

[Roy_Laudenslager]

I'm not familiar with Windows 10 but a logical analysis of the results of your test results indicated 12-hour system clock with AM and PM being reversed. I saw this problem several times when I was in ACT! tech sypport.