dbFailOnError SQL Server datetime bug for time-only values
<p>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:</p>

<pre><code>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
</code></pre>

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

<pre><code>UPDATE "dbo"."Appeals" SET HearingTime={t '09:00:00'} 
</code></pre>

<p>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.</p>

<p>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.</p>

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

<pre><code>9:00:00 AM
</code></pre>

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

<pre><code>9/3/2016 9:00:00 AM
</code></pre>

<p>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?</p>

<hr>

<p>*Yes I am aware there is a <code>time</code> 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.</p>