UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Time Changing Between SQL Server And Access 2013, Access 2013    
 
   
KDavidP1987
post Jun 13 2018, 12:38 PM
Post#1



Posts: 10
Joined: 12-June 18



Greetings all,

Context:
I'm having a conflict with data in an Access 2013 DB I'm developing for a team at work. The database connects (with linked tables) to a ticketing system on the backend, ran through SQL Server. I've setup the linked tables through ODBC connection successfully and been querying data, then I came across some discrepancies.

In one particular series of queries I am selecting from a linked table which holds Time-Tracking data. Time-tracking data records each time an agent works a ticket by recording the date and time of each event as a sequence paired with the ticket number (dual PKs).

The problem is: when I run the query for tickets worked by agents in my DB I get one number, and when I complete the process through our old method (which takes considerable time) I get different results (both positive and negative differences in the count)

Note: in the old method, we use the front end proprietary application of the company who developed the ticketing system and database to export the same time-tracking data to excel, remove duplicate entries for agents on each ticket, and then use Pivot Tables to calculate the count of tickets worked by each agent within a given date/Time frame.

My Question:
While I was playing around with the query, trying to get matching data, or explain the discrepency, I noticed that the date/time format within the linked tables data was different from the source data in SQL server! In SQL Server, the data is stored in yyyy/m/dd h:nn:ss:__ (000 - nanoseconds at the end), shown in attachment. When it comes over to Access 2013 the data is changed to 12-hour format.

I thought: "I'll just change the format of the date time and that should fix the issue..."

So I changed the field to Date/Time format yyyy/m/dd h:nn:ss. However, the data itself is STILL showing up changed through the link

Example: An agent who worked a ticket at 13:00:00:000 in the SQL Server is showing up as working the same ticket at 1:00 AM in the Access DB (01:00:00 when I changed format to military time).

So, the data itself is being changed in transition somehow, and I don't know how to address that........

Sadly, I don't even know if addressing this will fix the differences in count of tickets worked. However, i know that by playing with the Date/Time within the query itself I was able to produce different results! Which leads me to believe this issue may be at least part of the problem, if not the root.

Sincerely,
Kris

Update: Wanted to note that the ODBC connection I am utilizing is User DSN SQL Server Native Client 11.0

(Image of Date/Time format in SQL Server)
This post has been edited by KDavidP1987: Jun 13 2018, 12:44 PM
Attached File(s)
Attached File  Capture1.PNG ( 3.66K )Number of downloads: 0
 
Go to the top of the page
 
AlbertKallal
post Jun 13 2018, 04:27 PM
Post#2


UtterAccess VIP
Posts: 2,633
Joined: 12-April 07
From: Edmonton, Alberta Canada


Hum, it should not matter.

As long as access sees the column as a date/time, then you should not see a “change” in the time/date values.

You “often” do have to set the format on the Access client, but you should not see a “change” in the actual time/date values.

Native 11 or later is required for the “new” datetime2 formats from Access. If you use the older “SQL driver”, then such values come down as strings. Since you are using the newer ODBC driver, then you should not be seeing this issue.

I suppose I would check if your workstation regional time settings are correct – they may well be different from the server.

Regards,
Albert D. Kallal (Access MVP, 2003-2017)
Edmonton, Alberta Canada

Go to the top of the page
 
theDBguy
post Jun 13 2018, 04:43 PM
Post#3


Access Wiki and Forums Moderator
Posts: 72,396
Joined: 19-June 07
From: SunnySandyEggo


Hi Kris,

Welcome to UtterAccess!
welcome2UA.gif

I agree with Albert, the data's value should be (are) the same between Access and SQL. The difference you're seeing is merely the "presentation" of the data. I can't say for sure how it affects data consumed by Access from SQL Server but I know the Access date/time data type does not account for milliseconds; whereas SQL Server does. But other than that, if SQL Server says it's 13:00, then Access also sees the data as 13:00; although, it might reformat the data to display 01:00 (for some unknown reason, one being, possibly, is your Regional Settings, as Albert mentioned.

Just my 2 cents...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
KDavidP1987
post Jun 14 2018, 10:02 AM
Post#4



Posts: 10
Joined: 12-June 18



Hey guys,

Thank you so much for the warm welcome to UtterAccess, and the information on my inquiry!

I believe I was hasty in assuming that was the issue, you were correct and I greatly appreciate the insights!! That particular agent has been working his shift, plus filling in for another through the night, and I wasn't aware. However, there is still a problem in Time/Date input causing the inconsistent data and I don't know how to write my query to resolve it...

So, when ran the query prompts for a date range parameters, using Between [Enter Start Date:] And [Enter End Date:]. I found that the differences in count of tickets worked changes when I enter the parameter as date vs. Date/Time. (see pictures)

When entered as 05/01/2018 thru 05/31/2018 for a single agent, I got 960 tickets worked. However, when I entered the dates as 05/01/2018 00:00:00 thru 05/31/2018 23:59:59 I got a more accurate count of 1021 tickets worked.

I feel sure this is an amateur mistake, but do you have an idea how I can correct this so the time doesn't have to be entered?

Sincerely,
Kristopher



Attached File(s)
Attached File  Capture1.PNG ( 12.18K )Number of downloads: 0
Attached File  Capture2.PNG ( 1.06K )Number of downloads: 0
Attached File  Capture3.PNG ( 10.05K )Number of downloads: 0
Attached File  Capture4.PNG ( 10.51K )Number of downloads: 0
Attached File  Capture5.PNG ( 965bytes )Number of downloads: 0
 
Go to the top of the page
 
theDBguy
post Jun 14 2018, 10:05 AM
Post#5


Access Wiki and Forums Moderator
Posts: 72,396
Joined: 19-June 07
From: SunnySandyEggo


Hi Kristopher,

Just a hunch but what result do you get if you entered the range 05/01/2018 thru 06/01/2018?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
KDavidP1987
post Jun 14 2018, 10:11 AM
Post#6



Posts: 10
Joined: 12-June 18



Wow, what a great insight!!!

When I expanded the date range to 05/01/2018 - 06/01/2018 I received the proper 1021. I feel like a bit of a dunce for not thinking of that, as a possibility.

So that means the BETWEEN statement essentially includes data for dates up to but not including the last date? I had assumed it was the same as saying >= 05/01/2018 and <= 05/31/2018.

Sincerely,
Kristopher
Go to the top of the page
 
PhilS
post Jun 14 2018, 10:26 AM
Post#7



Posts: 477
Joined: 26-May 15
From: The middle of Germany


QUOTE
<= 05/31/2018.

This is 05/31/2018 00:00:00!
So, by using this criteria you will be missing all records entered on this day past midnight.

--------------------
New article: The Access ADP Story
Go to the top of the page
 
KDavidP1987
post Jun 14 2018, 10:28 AM
Post#8



Posts: 10
Joined: 12-June 18



Okay, this is odd.... When I changed the expression to >=[Enter Start Date:] And <=[Enter End Date:], I received the same count of 960 tickets worked....

I don't think I'm understanding completely how Access is processing Date/Time. It doesn't make sense to me why it wouldn't include tickets from the dates entered.

Sincerely,
Go to the top of the page
 
KDavidP1987
post Jun 14 2018, 10:30 AM
Post#9



Posts: 10
Joined: 12-June 18



Really! I didn't realize it was auto-completing the date entered to include 00:00:00 as a sort of default value...

Is there any way to write the expression so it automatically includes the entire day without having the user enter 23:59:59 each time?

Sincerely,
Go to the top of the page
 
theDBguy
post Jun 14 2018, 10:33 AM
Post#10


Access Wiki and Forums Moderator
Posts: 72,396
Joined: 19-June 07
From: SunnySandyEggo


Try changing the criteria to something like this:

BETWEEN [Enter Start Date] AND [Enter End Date] + #23:59:59#

Alternatively, you could try something like this:

>=[Enter Start Date] AND <=[Enter End Date] + #23:59:59#

Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
KDavidP1987
post Jun 14 2018, 10:41 AM
Post#11



Posts: 10
Joined: 12-June 18



Hey DBGuy,

I tried your suggestion, both ways, and received this error upon entering the parameters (see picture).

Note: interestingly, each time I would enter the expression as + #23:59:59# it would auto-correct to +#11:59:59 PM#

>=[Enter Start Date:] And <=[Enter End Date:]+#11:59:59 PM#

Sincerely,

Attached File(s)
Attached File  Capture.PNG ( 28.27K )Number of downloads: 4
 
Go to the top of the page
 
KDavidP1987
post Jun 14 2018, 10:51 AM
Post#12



Posts: 10
Joined: 12-June 18



I think I figured out a solution, I had to change it to this:

>=[Enter Start Date:] And <=DateAdd("d",+1,[Enter End Date:])

It seems to work now.

Sincerely,
Kristopher
Go to the top of the page
 
theDBguy
post Jun 14 2018, 10:51 AM
Post#13


Access Wiki and Forums Moderator
Posts: 72,396
Joined: 19-June 07
From: SunnySandyEggo


Hi,

The error message tells me there might be some Null values in your table. Could you please double-check? Thanks.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
PhilS
post Jun 14 2018, 01:24 PM
Post#14



Posts: 477
Joined: 26-May 15
From: The middle of Germany


QUOTE
>=[Enter Start Date:] And <=DateAdd("d",+1,[Enter End Date:])

Remove the equals-sign in front of DateAdd, otherwise your query will include 06/01/2018 00:00:00.

I wrote a text on Date (and Time) in VBA and Access. It explains the whole topic, including your particular scenario here, quite thoroughly.


--------------------
New article: The Access ADP Story
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    19th June 2018 - 08:50 PM