dbFailOnError SQL Server datetime bug for time-only values

Question

I have a linked table in SQL Server with a datetime column where I store a time-only value*. If I execute an UPDATE query without dbFailOnError it translates that command into a SELECT followed by individual UPDATE statements that execute one row at a time:

exec sp_executesql N'UPDATE "dbo"."Appeals" SET "HearingTime"=@P1 
                     WHERE "AppealID" = @P2'
                  ,N'@P1 datetime,@P2 int','1899-12-30 09:00:00',1
...
exec sp_executesql N'UPDATE "dbo"."Appeals" SET "HearingTime"=@P1 
                     WHERE "AppealID" = @P2'
                  ,N'@P1 datetime,@P2 int','1899-12-30 09:00:00',4

If I execute the exact same UPDATE query, but with dbFailOnError, I get this translation:

UPDATE "dbo"."Appeals" SET HearingTime={t '09:00:00'} 

It is interesting that the dbFailOnError forces a more efficient UPDATE on the back end, but my real concern is with the time value itself.

In the first example, Access correctly sets the datetime to 12/30/1899 (MS Access's magic "zero" day). In the second case, that does not happen. The end result is that the first example "works" and the second one does not.

What I mean by that, is that if I view the HearingTime field in datasheet view, Access shows the first one as:

9:00:00 AM

And the second one shows as (as of this writing, 9/3/16 is today's date):

9/3/2016 9:00:00 AM

I have to assume this is a bug on Microsoft's part. Or am I missing something here? Do I have a better option than reporting the bug and just hoping that Microsoft fixes it someday?

---

*Yes I am aware there is a time datatype in SQL Server. It is not compatible with the MS Access datetime type, so it's of little use to me in an MS Access linked table.

Answer 1

I was able to reproduce your issue using queries with Access SQL Date/Time literals. Both

Dim cdb As DAO.Database
Set cdb = CurrentDb
cdb.Execute "UPDATE dbo_Appeals SET HearingTime=#10:00:00#", dbFailOnError

and

Dim cdb As DAO.Database
Set cdb = CurrentDb
cdb.Execute "UPDATE dbo_Appeals SET HearingTime=#1899-12-30 11:00:00#", dbFailOnError

resulted in [HearingTime] values with the current date (2016-09-03), not the "zero" date.

However, passing the full date/time as a string seems to work

Dim cdb As DAO.Database
Set cdb = CurrentDb
cdb.Execute "UPDATE dbo_Appeals SET HearingTime='1899-12-30 12:00:00'", dbFailOnError

(Omitting the date part in the string value results in [HearingTime] values with the SQL Server DATETIME "zero" date of 1900-01-01.)

And, even better, a parameterized query with a DAO.QueryDef object also appears to work correctly

Dim cdb As DAO.Database
Set cdb = CurrentDb
Dim qdf As DAO.QueryDef
Set qdf = cdb.CreateQueryDef("", _
        "PARAMETERS prmHearingTime DateTime;" & _
        "UPDATE dbo_Appeals SET HearingTime=[prmHearingTime]")
qdf!prmHearingTime = #10:00:00 AM#
qdf.Execute dbFailOnError

Answer 2

That's a good question, and I can reproduce it.

It seems to be a bug in the ODBC driver, because the behaviour you demonstrate is that of SQL Server which the ODBC driver should compensate for, as it does in your first example by providing the full date-time string that will translate correctly when read back in Access.

I can see no way to work around it other than, when you read back the data, to apply TimeValue to the date-time values. This, however, will cease any use of an index of your time field.

And data type Time of SQL Server cannot be used for many things as the ODBC driver reads these as text.