That is a nice trick that dateadd(yy, datediff(yy, 0, @ThisDate) + 1, 0) returns the first moment of next year. However, it does have the downside that if @ThisDate is one of the newer date&time data types (i.e., datetime2, datetimeoffset), the result is converted back to the old datetime data type.

This is because DATEDIFF just returns an integer, losing all concept of the data type of @ThisDate, and then DATEADD, with only integers as arguments, defaults to the datetime datatype.

If the result is cast to datetime2 (either explicitly, or implicitly by assignment to a variable or column of the data type), the loss of microseconds and nanoseconds doesn't really matter since the idea is to zero out the fractional part anyway. However, with datetimeoffset one may need to retain the timezone offset:

declare

@ThisDate datetimeoffset(7) = SYSDATETIMEOFFSET()

;

select

[Input] = @ThisDate

, [LoseTZ] = CAST(DATEADD( ss, -1, DATEADD(yy, DATEDIFF(yy, 0, @ThisDate) + 1, 0) ) AS datetimeoffset(7))

, [WithTZ] = TODATETIMEOFFSET(DATEADD( ss, -1, DATEADD(yy, DATEDIFF(yy, 0, @ThisDate) + 1, 0) ), DATEPART(tz, @ThisDate))

;