T-SQL : add Time to Datetime2

Question

I want to add up a DateTime2 and Time(7) values

DECLARE @createdAt datetime2 = '2023-03-11 23:08:56.990'
DECLARE @dayOffset time(7) = '04:00:00'

So that by adding the 2 values I'd get '2023-03-12 03:08:56.990'
Eventually I also want to do the same thing but by subtracting so I'd get '2023-03-11 19:08:56.990'

I found different ways to add both of theses values but it always removes the time part of DateTime

DECLARE @createdAt datetime2 = '2023-03-11 23:08:56.990'
DECLARE @dayOffset time(7) = '04:00:00'

SELECT
    DATEADD(DAY, DATEDIFF(DAY, @dayOffset, @createdAt), CONVERT(DATETIME2, @dayOffset))

SELECT 
    DATEADD(DAY, DATEDIFF(DAY, COALESCE(@dayOffset, '00:00:00'), @createdAt), CONVERT(DATETIME2, COALESCE(@dayOffset, '00:00:00')))

But both return 2023-03-11 04:00:00.0000000.

What would be the proper way to do this?

Answer 1

Here's one way:

DECLARE @createdAt datetime2 = '2023-03-11 23:08:56.990'
DECLARE @dayOffset time(7) = '04:00:00'

select dateadd(ms, datediff(ms, 0, @dayOffset), @createdAt) -- add
select dateadd(ms, -datediff(ms, 0, @dayOffset), @createdAt) -- subtract

Basically, it converts the time value into milliseconds, and then adds / subtracts it to your date