DateTimeOffset.Now in T-SQL

Maybe.

This would give you the local time of the server where SQL is installed.

Do you want to store timezone etc too? If so, SYSDATETIMEOFFSET() may be better