January 26, 2005 at 7:25 am
Where i work, the developers have a database that holds common functions used by several other databases. I thought that was kinduv weird, but then I found that some of the functions do simple things like strip the time info from a datetime. So I mentioned just using CONVERT(DATETIME, @DateVal, 101) [or any other style setting you need], but then when I went to compare the efficiency, it looks like the CONVERT() actually takes longer than their function that CASTs and DATEPARTs the passed-in date! Need DB Guru Help on this.
--// TransactSQL starts here ------------------------------------------------
declare @theDate datetime
set @thedate = getdate()
--// Now, compare using this homegrown function (see at bottom of this message)
set @thedate = functions.dbo.fn_ShortDate(@thedate)
--// versus SQL Server's CONVERT
set @thedate = CONVERT (DATETIME , @thedate , 101 )
--// The 'Show Client Statistics' tab makes it look like the
--// homegrown might be faster, even though it's on another
--// database and CASTing and DATEPARTing all over the place!
--// homegrown function ----------------------------------
CREATE FUNCTION fn_ShortDate (@Date as SMALLDATETIME)
RETURNS SMALLDATETIME
AS
BEGIN
RETURN CAST(
CAST(DATEPART(mm,@Date) AS VARCHAR(2)) + '/' +
CAST(DATEPART(dd,@Date) AS VARCHAR(2)) + '/' +
CAST(DATEPART(yy,@Date) AS VARCHAR(4))
AS SMALLDATETIME
)
END
January 26, 2005 at 9:20 am
DATEADD(d, DATEDIFF(d, 0, @YourDate), 0)
This works for smalldatetime and datetime, and does not involve conversions to other datatypes.
January 26, 2005 at 9:42 am
One thing with convert is it does do an extra read but only one to get the format from the syslanguages table if I remember correctly.
But unless you freed the proccache and dropped clean buffer using the associated DBCC command you may have actually gotten incorrect comparisons in performance but you also have to keep in mind unless you cast back to a datetime datatype sql may have choosen to implicitly convert to char datatype as your convert statements output will be instead of converting it to datetime, I suggest always be explicit in typing.
But if you are just want to strip the time to 0 which is midnight and keep a datetime datatype as shown in you example then Phil post is what I would use so no conversions of datatype are needed.
January 26, 2005 at 12:19 pm
THANKS guys - good info - what metric-tracking tools in query analyzer do you recommend for comparing these functions? I'd like to find a more deterministic way of comparing these functions:
(DATEADD(d, DATEDIFF(d, 0, @YourDate), 0) vs CONVERT (DATETIME , @thedate , 101 ) , etc.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply