Time queries in Access to MS SQL Server via ODBC (1 Viewer)

[Galaxiom]

A surprise when querying Time using Access (2007) from MS SQL Server (2005) table via ODBC.

SQL Server DateTime field. Access Zero Date (12/30/1899) used as the date component so Access automatically shows the time only in the linked table.

This field cannot be sensibly queried by Access using comparative operators (>, =, < , Between etc).

Equating to a time in a query finds no matching record. Bewilderingly, Less-Than and Greater-Than queries actually return the opposite of what they should.

Including 12/30/1899 in the query criteria is discarded as it is everywhere in Access.

The issue appears to be the interpretation of the zero date in the records by Access. The date in the criteria of the Access query is treated correctly as 12/30/1899 if the date in the records any other date.

The comparison must be made using DateDiff even though the comparative operators work fine on other dates from SQL Server and inside Access tables.

DAO recordset queries respond similarly. I am about to try using ADO.

(BTW The Zero Date in SQL Server is 1/1/1900 but that isn't important here.)

 

[Rx_]

I just migrated from Access 2010 back-end to SQL Server 2008 RC2. The Dates are a real problem. The date picker won't work and the format is all messed up now. Just finished this morning and used odbc linked tables.
Please post any worthwhile comments on your own question and I will do the same. I have a ton of queries in reports to deal with too.

Somehow the quote from the movie 'the 300' "you are not going to enjoy this" comes to mind.

 

[Galaxiom]

I heard that Access 2010 can work with the new DateTime format in SQL Server 2008 but earlier versions of Access need the server's TinyDateTime datatype.

 

[Rx_]

It didn't work for me. And, there are tons of blogs describing the same problem. I had to stay late as people left to push out a big release. Will make another go of it tomorrow morning. It is very concerning to me at this point. The SQL did convert to a Datetime, but Access is choking on all conversions.
Will share findings tomorrow.

 

[Rx_]

Think I have the answer now. (whew!)
Give me a couple of hours to re-link everything and do some testing.
It has put me behind schedule, so remind me.
What is bad is that I had this problem 3 years ago and posted a partial solution for Access 2003 on SQL Server 2008 migration from SQL Server 2000. Now I am mad at myself for not adding the tags to find it sooner this time around. Could give myself a good talking to, but would probably forget that too.

 

[Pat Hartman]

Interesting. I've never had a single problem with dates in SQL Server. What was your solution?

 

[Galaxiom]

It was really only a problem with DateTime between Access and SQL Server and using 30/12/1899 as the date component which I chose to make the Access table show Time only.

All is fine so long as the times are compared in Access using DateDiff. Generally speaking it is best to compare times using DateDiff anyway because of rounding errors when expressing Times as decimal fractions of a day, especially if looking for equality of two times.

The times I was working with had a resolution of one minute and I was querying for any records with a time before the record being compared. Normally in this case the decimal rounding errors would not be an issue.

The really weird thing was the reversal of results given by Greater-Than and Less-Than. I was lucky that my queries were highly dependent on the time and gave blatently wrong results but I could imagine a situation where such an error could go unnoticed.

I would be interested if others could reproduce the error. It seems like a bug in the ODBC layer.

 

[Rx_]

When I used the ODBC on SQL 2008 R2 / Access 2010 the dates in the attached tables were all turned into Text. The other people with the same problem were asked if the SQL table/field settings were set to text. They are not.
By downloading the Microsoft SQL Native Client 10 DLL and choosing it, the exact same front-end connected to the exact same SQL Server DB worked perfectlly as far as Date/Time goes in all of the date picker fields.
(see attachment).
For my citrix distribution environment, it will be necessary to add this DLL on the client server.

Today, I am working on a DNS-Less code. Will run code to take all the linked tables names and create a table. Then run the code to create the string to attach to the server. A couple of interesting articles indicate that a DNS-Less connection has much better response time overall.

 

[mdlueck]

When I used the ODBC on SQL 2008 R2 / Access 2010 the dates in the attached tables were all turned into Text.

I am developing with a SQL Server 2008 R2 server and Access 2007 SP3.

When I create Access Linked Tables, and inspect the Access properties of the linked tables, then both date formats I am using in SQL Server BOTH map through as text fields. On SQL Server I am making use of:

    [logtimestamp] [datetime2](0) NOT NULL,
    [duedate] [date] NOT NULL,

I am using unbound Add/Edit record forms, so the Access 2007 Date Picker is able to be utilized.

 

[Galaxiom]

I am working on a DNS-Less code.

I see this mistake more often than I see the correct term.

DNS = Domain Name Service

DSN = Data Source Name

 

[Rx_]

Thanks for pointing that out. - I honestly would not have seen that today. I will watch that closer.
Dyslexic Spelling Nonsense
in my case.