Posted by Alexander Kuznetsov on 09/19/06 23:17

Hi Erland,

I did a quick benchmarking. I always knew that DATEDIFF approach should
be faster than converting to CHAR, but I had no idea it is that faster:

--CONSTRAINT dateonly CHECK (convert(char(8), birthdate, 112) =
birthdate)

DECLARE @d1 DATETIME, @d2 DATETIME, @i INT, @cnt INT
DECLARE @d TABLE(ddd DATETIME)
SET NOCOUNT ON

SET @i = 0
WHILE @i<100000 BEGIN
INSERT @d VALUES('20060101')
SET @i = @i + 1
END
SET @d1 = GETDATE()
SET @i = (SELECT COUNT(*) FROM @d WHERE (convert(char(8), ddd, 112) =
ddd))
SET @d2 = GETDATE()
SELECT DATEDIFF(ms, @d1, @d2), 'char'

SET @d1 = GETDATE()
SET @i = (SELECT COUNT(*) FROM @d WHERE
(dateadd(d,datediff(d,'1990-01-01',ddd),'1990-01-01') = ddd))
SET @d2 = GETDATE()
SELECT DATEDIFF(ms, @d1, @d2), 'datediff'

-------------------------------------------------------

----------- ----
346 char

----------- --------
46 datediff

• Next in forum: Cannot Open .bak File from ms sql database. either 2000 or 2007
• Prev in forum: Re: formatting for a birth date field??
• Thread view: Re: formatting for a birth date field??

[Reply to this message]