Is there any performance implication from using varchar(max) over varchar(n) in cast/convert?

Question

Consider the following expression, which truncates (does not round) the milliseconds from a date-time value:

declare @now datetime2 = sysdatetime();
select @now;
select convert(datetime2, convert(varchar(20), @now, 120));

-- Output
2021-07-30 09:38:33.5566666
2021-07-30 09:38:33.0000000

Notice the varchar(20). I don't like that specific length value because if I should change my datatypes there could be data loss:

declare @now datetimeoffset = sysdatetimeoffset() at time zone 'Pacific Standard Time';
select @now;
select convert(datetimeoffset, convert(varchar(20), @now, 120));

-- Output
2021-07-30 02:39:12.7200000 -07:00
2021-07-30 02:39:12.0000000 +00:00 -- oops, we lost the time zone too!

Hence I'd much rather use the following:

declare @now datetimeoffset = sysdatetimeoffset() at time zone 'Pacific Standard Time';
select @now;
select convert(datetimeoffset, convert(varchar(max), @now, 120)); -- note MAX not N

-- Output
2021-07-30 02:41:16.4566666 -07:00
2021-07-30 02:41:16.0000000 -07:00

My question is, is there any sort of meaningful performance implication in using varchar(max) over varchar(N) - including but not limited to memory allocations?

I'm aware there are implications for query performance if using (max) datatypes over (N) datatypes in predicates, but in my particular examples I'm not doing that - just allocating the varchars then throwing them away after converting them back to the desired datatype.

Answer 1

First off, to answer your question:

Yes, it can affect performance, as space needs to be allocated to hold large values in the query engine.

In your case, you could also use a suitably large size such as varchar(50) which would easily hold whatever you needed.

But you shouldn't be doing any of this in the first place.

When rounding dates, you should not convert to varchar and back, as this has poorer performance, and issues involving culture/style.

Instead, use standard rounding techniques:

declare @now datetimeoffset = sysdatetimeoffset() at time zone 'Pacific Standard Time';
declare @epoch datetimeoffset = CAST(@now AS date);
select dateadd(
    second,
    datediff(second, @epoch, @now at time zone 'utc'),
    @epoch) at time zone 'Pacific Standard Time';