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 varchar
s then throwing them away after converting them back to the desired datatype.