2

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.

2 Answers 2

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';

You can use any date for @epoch, it's just a point to measure by. As long as you use the same value in both places it will work.

0
1

In SQL Server 2022 and later, you can use the DATETRUNC built in function:

Starting with SQL Server 2022 (16.x), this function returns an input date truncated to a specified datepart.

DECLARE @now datetimeoffset = 
    SYSDATETIMEOFFSET() 
    AT TIME ZONE 'Pacific Standard Time';

SELECT 
    Original = @now, 
    Truncated = DATETRUNC(SECOND, @now);
Original Truncated
2023-04-01 02:12:01.7056958 -07:00 2023-04-01 02:12:01.0000000 -07:00
DECLARE @now datetime2(7) = 
    SYSDATETIME();

SELECT 
    Original = @now, 
    Truncated = DATETRUNC(SECOND, @now);
Original Truncated
2023-04-01 22:12:01.7516845 2023-04-01 22:12:01.0000000

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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