Wrapper Function For Date Truncation

Question

I'm not a fan of working with dates in SQL Server and can never remember how to do some simple tasks off the top of my head.

One of them is truncating today's date. convert(datetime, datediff(day, 0, getdate()))

I wanted to start creating wrapper functions for these for readability and reference.

create or alter function
    /* Returns the date as of today at midnight (truncated)
     *
     * i.e.
     *   getdate() -> 2023-12-04 10:26
     *   date_today_midnight() -> 2023-12-04 00:00
     */
    dbo.date_today_midnight()
returns
    datetime
as begin
    return convert(datetime, datediff(day, 0, getdate()));
end

Unfortunately there's a big performance hit when I swap my new function into the where clause.

select * from aTable where aDate >= convert(datetime, datediff(day, 0, getdate()));
-- runtime < 1s

select * from aTable where aDate >= date_today_midnight();
-- runtime > 10s

From my understanding, the problem is related to getdate() being a runtime constant function. So SQL server knows to swap that out with a constant at the beginning of query execution. Is there a way I can mark my own function as a runtime constant?

I tried using a CTE to get the date first, but that somehow resulted in even worse performance.

with dates as (select date_today_midnight() as today)
select * from aTable join dates on 1=1 where aDate >= dates.today;
-- runtime > 50s

Answer 1

Don't use the method you have, it hasn't been needed since SQL Server 2005. The date data type was added in SQL Server 2008 so there is no reason to use a "quirky" solution like you have here. if you want get the current date, as a date, then just CONVERT/CAST the value to a date:

SELECT CONVERT(date,GETDATE()) AS CurrentDate;

As for using a function, again don't. User Defined Scalar functions are known to not be performant, as historically they weren't inlinable. In 2019+ (not what you are using) inlinable scalar functions were added. however, they have had also had performance issues, and the "solution" has often been for Microsoft add more and more requirements (caveats) to causing a function to be (not) inlinable. Using GETDATE() in scalar function will cause it to not be inlinable:

A scalar T-SQL UDF can be inlined if all of the following conditions are true:

• ...
• The UDF doesn't invoke any intrinsic function that is either time-dependent (such as GETDATE()) or has side effects ³ (such as NEWSEQUENTIALID()).
• ...

³ Intrinsic functions whose results depend upon the current system time are time-dependent. An intrinsic function that may update some internal global state is an example of a function with side effects. Such functions return different results each time they're called, based on the internal state.

You could use an inline table value function, however, for something as trivial as this, there is entirely no need for such a function. CONVERT/CAST the value to a date is more than simple enough.

If you were in SQL Server 2022+, you would also have access to the DATETRUNC function:

SELECT DATETRUNC(DAY, GETDATE());

Unlike CONVERT/CAST, this would retain the data type of the input expression, so would return 2023-12-04T00:00:00.000 rather than 2023-12-04.

Answer 2

Just for posterity, here's an inline version if your function:

create or alter function
    /* Returns the date as of today at midnight (truncated)
     *
     * i.e.
     *   getdate() -> 2023-12-04 10:26
     *   date_today_midnight() -> 2023-12-04 00:00
     */
    dbo.date_today_midnight()
returns table as
    return select convert(datetime, datediff(day, 0, getdate())) as today

Usage style #1:

select *
from sys.objects so
where so.create_date < (select today from dbo.date_today_midnight())

Usage style #2:

select *
from sys.objects so
CROSS APPLY dbo.date_today_midnight() t
where so.create_date < t.today

If you see the QP selection, you will notice that function call has been inlined:

[master].[sys].[sysschobjs].[created] as [o].[created]<CONVERT(datetime,datediff(day,'1900-01-01 00:00:00.000',getdate()),0) AND [master].[sys].[sysschobjs].[nsclass] as [o].[nsclass]=(0)
AND [master].[sys].[sysschobjs].[pclass] as [o].[pclass]=(1)

Answer 3

I ended up using an inline TVF. It's not exactly what I wanted but more or less accomplishes what I was going for with no noticeable impact to the query time.

create or alter function
    tvf_dates(@date datetime)
returns
    table
as return (
    select
        @date as reference_date,
        cast(@date as date) as midnight,
        dateadd(day, -1, @date) as previous,
        cast(dateadd(day, -1, @date) as date) as previous_midnight,
        dateadd(day, 1, @date) as next,
        cast(dateadd(day, 1, @date) as date) as next_midnight,
        getdate() as now,
        cast(getdate() as date) as today,
        dateadd(day, -1, getdate()) as yesterday,
        cast(dateadd(day, -1, getdate()) as date) as yesterday_midnight,
        dateadd(day, 1, getdate()) as tomorrow,
        cast(dateadd(day, 1, getdate()) as date) as tomorrow_midnight
)

select aTable.* from aTable cross join tvf_dates(getdate()) where aDate >= dates.today;