quote:

---

Philip, I took some time and tested your function against DateDiff-DateAdd.

CREATE FUNCTION dbo.fnDateOnlyBinary

(@Date datetime)

returns datetime

AS

BEGIN

RETURN cast(substring(cast(@date as binary(8)),1,4) + 0x00000000 as datetime)

END

CREATE FUNCTION dbo.fnDateOnly_DateDiff

(@Date datetime)

returns datetime

AS

BEGIN

RETURN DATEADD(d,DATEDIFF(d,0,@Date),0)

END

Here were the results on a million dates.

--Time: 55

--CPU: 13312

--IO: 116

select top 1000000 dbo.fnDateOnlyBinary(DT)

From #Date

--Time: 55

--CPU: 11063

--IO: 234

select top 1000000 dbo.fnDateOnly_DateDiff(DT)

From #Date

Your function used about 25% more cpu cycles, but only half the IO. It appears that Datediff and DateAdd cost IO but save a little on CPU, while casting and substring costs no IO and more CPU.

I'm thinking I'll replace my current "day only"

function with this one, as IO is a bottleneck for us. Of course, even the datediff-dateadd method is better than the cast-convert I'm using now.

Good stuff.

---