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 3 (such as NEWSEQUENTIALID() ).
• ...

3 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 .

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)

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;