how to cast datetime into date in sql 2005
This question already has an answer here:
You can try this:
convert(DateTime, floor(convert(float, getdate())))
That gets rid of the time part, by converting it into the underlying float number and rounding down to the nearest integer. The integer part represents the date and the decimal part is the time, so converting the integer back to a DateTime give the same date you started with but without any time part.
You can also wrap it up as a scalar-valued function:
CREATE FUNCTION [dbo].[DateTrunc] (@date DateTime)
RETURNS DateTime
AS
BEGIN
return convert(DateTime, floor(convert(float, @date)))
END