For the next few months, I will be involved in an interesting project for a mission critical application that our company have outsourced to a well-known and respected consulting company here Sweden.
My part will be the technical details of the forecasting application now when our former DBA has left our company.

Today I took a brief look at the smallest building blocks; the Functions. No function is inline so I can assume some of the performance issues are derived from these.

One function I stumled across is very simple. All it does is to add a timepart from current execution time to the datepart from a variable.


CREATE FUNCTION dbo.GetDateTimeFromDate
(
    @p_date DATE
)
RETURNS DATETIME
AS
BEGIN
    DECLARE @ActualWorkDateTime DATETIME

    SET @ActualWorkDateTime = CONVERT(VARCHAR, @p_date, 101) + ' '+ CONVERT(VARCHAR, GETDATE(), 114)

    RETURN  @ActualWorkDateTime
END


This doesn't look to bad compared to what I have seen on the online forums. But there is a hidden performance issue here, besides being not an inline function, and that is the conversion to varchar and back to datetime. Also, this functions crashed in my tests when I changed dateformat to dmy. This is because the developer used style 101 in the convert function. If he had used style 112 the function would not have crashed no matter which dateformat value I use.

So to our next meeting I will explain to the consultants the issues I have with this function and the others that I've found.
A better choice for this function would be


CREATE FUNCTION dbo.GetDateTimeFromDate
(
    @p_date DATE
)
RETURNS DATETIME
AS
BEGIN
    RETURN  (
                SELECT  DATEADD(DAY, DATEDIFF(DAY, GETDATE(), @p_date), GETDATE())
            )
END


See, now there is no conversion, it's inline and dateformat-safe! A generic function for adding the date part from one variable to the time part from another variable looks like this


CREATE FUNCTION dbo.fnGetDateTimeFromDatePartAndTimePart
(
    @DatePart DATETIME,
    @TimePart DATETIME
)
RETURNS DATETIME
AS
BEGIN
    RETURN  (
                SELECT  DATEADD(DAY, DATEDIFF(DAY, @TimePart, @DatePart), @TimePart)
            )
END