This simple user function will return the beginning of the day given a datetime in SQL.
ALTER FUNCTION [dbo].[fnBeginningOfDay](@date DATETIME)
RETURNS DATETIME
AS
BEGIN
DECLARE @finalDate DATETIME
SET @finalDate = (SELECT CAST(DATEPART(mm,@date) AS VARCHAR(5)) + '/' + CAST(DATEPART(dd,@date) AS VARCHAR(5)) + '/' + CAST(DATEPART(yyyy,@date) AS VARCHAR(10)))
RETURN @finalDate
END
You can test it with the following:
SELECT dbo.fnBeginningOfDay('01/02/2009 06:30:00 AM')
SELECT dbo.fnBeginningOfDay(getDate())
af4ce0c2-65ef-4327-8b9e-a696264fc3bd|0|.0
Microsoft SQL
sql 2005