1

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.

5

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';
8
  • Where does that @epoch value of '20200101' come from?
    – Ian Kemp
    Jul 30 at 14:07
  • You can use any date, it's just a starting point to measure by, and as long as you use the same value in two places it will work Jul 30 at 15:37
  • The epoch needs to be within “int” units of the DATEDIFF. Using DATEDIFF_BIG extends that to “big int”. This becomes very relevant when dealing with units of time like millis (or smaller) that can easily overflow, or when dealing with non-contemporary dates. Additionally, to just truncate, use the DATE of the time as the epoch. Jul 31 at 5:04
  • @user2864740 Good points, have modified. DATEDIFF_BIG is useless without DATEADD_BIG which does not exist unfortuantely Aug 1 at 2:29
  • 1
    @IanKemp Of course you can do it in one query, you can just declare it in a CROSS APPLY (VALUES Aug 2 at 9:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.