TSQL - Round up/down to nearest SECOND

Question

Code:

DECLARE @var DATETIMEOFFSET(7) ;

-- Rounding up
SET @var = '2020-03-20 12:00:29.9999999 -08:00' ;
SELECT  DATEADD ( ms, 500 - DATEPART ( ms, DATEADD ( MILLISECOND, 500, @var )), @var ) ;
-- Actual: 2020-03-20 12:00:30.0009999 -08:00
-- Expected: 2020-03-20 12:00:30.0000000 -08:00 -- Notice that precision is rounded up to '0000000'

-- Rounding down
SET @var = '2020-03-20 12:00:30.0000001 -08:00' ;
SELECT  DATEADD ( ms, 500 - DATEPART ( ms, DATEADD ( MILLISECOND, 500, @var )), @var ) ;
-- Actual: 2020-03-20 12:00:30.0000001 -08:00
-- Expected: 2020-03-20 12:00:30.0000000 -08:00 -- Notice that precision is rounded down to '0000000'
GO

Goal: I want to round to the nearest seconds based on milliseconds < 5###### or milliseconds >= 5######. The query that I have so far does round up the seconds correctly, it's just not rounding the precision to what I want, 0000000.

Answer 1

Casting to the target precision will handle the rounding for you:

select cast(@var as datetimeoffset(0));

If you still want a result as datetimeoffset(7), then you can cast twice:

select cast(cast(@var as datetimeoffset(0)) as datetimeoffset(7));