How To Quickly Verify That A Date Has No Time Part
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 (at least 7 times faster):
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
0 Comments:
Post a Comment
<< Home