We have two functions below which are basically identical except the actual method of stripping off the time portion of the datetime variable.
CREATE FUNCTION [dbo].[DateTimeToDate](@DateTime datetime)
RETURNS DateTime
AS
BEGIN
return CONVERT(VARCHAR(10),@DateTime,111)
END
CREATE FUNCTION [dbo].[DateTimeToDate](@DateTime datetime)
RETURNS DateTime
AS
BEGIN
return cast(cast(@DateTime as integer) as DateTime)
END
declare @StartDateTime datetime
declare @EndDatetime datetime
declare @dt datetime
declare @Count int
set @StartDateTime = getdate()
set @Count = 0
while (@Count < 10000000)
begin
set @dt = dbo.DateTimeToDate('2007-10-1 1:45pm')
set @Count = @Count+1
end
set @EndDateTime = getdate()
select @EndDateTime - @StartDateTime
We run the script above which calls the function 10 million times and the results are quite close
| 286.157 seconds |
return CONVERT(VARCHAR(10),@DateTime,111) |
| 250.170 seconds |
return CAST(CAST(@DateTime as integer) as DateTime) |