How to update a SQL Server DateTime column to a value that is interpreted as a time by Microsoft Access

When upsizing an MS Access database to SQL Server 2000, one issue that may arise is if you are displaying times in the Access front end from a SQL DATETIME data type column. If this is the case, Access interprets datetimes with the date '1899-12-30' as a time without a date part. So to save a value to a SQL DATETIME column formatted for correct display as a time in Access you need to replace the date part as follows:

CREATE FUNCTION [dbo].[fGetTimePartForDBUpdate](
@fullDateTime datetime)
RETURNS DATETIME
/*
Return time formatted for display in a Microsoft Access table.
*/
AS
BEGIN

DECLARE @datePart DATETIME,
@timePart DATETIME

SET @datePart = CAST(CONVERT(CHAR(10), @fullDateTime, 101) AS DATETIME)

SET @timePart = @fullDateTime - @datePart

RETURN CAST('1899-12-30 ' AS DATETIME) + @timePart
END