As to the actual code, a more straightforward way is to CAST to date, which will perforce remove the time value, then CAST back to datetime (this probably isn't strictly needed, but it aids in clarity
/*you want to be sure these settings are on before creating any object in SQL Server*/
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE FUNCTION DBO.TFNGETDATEONLY ( @InDate DATETIME )
RETURNS DATETIME
BEGIN
RETURN CAST(CAST(@InDate AS date) AS DATETIME) /*strip time/force time to midnight*/
/*end of function*/
END
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!