CURRENT_DATE, || and Other Small T-SQL Changes in SQL Server 2025

A small syntax improvement can remove a surprising amount of repeated code. CURRENT_DATE and several companion changes arrive in SQL Server 2025 and later. They shorten familiar expressions while leaving time zones, NULL handling, types, and range limits as decisions you still need to make.

Check the Engine Before Using CURRENT_DATE

These examples target SQL Server 2025 or later. Verify the connected engine, especially when a script moves among development, reporting, and older administrative servers. An up to date SSMS installation cannot add language features to an older engine. SQL is executed by the target server, regardless of how familiar the new syntax looks in the editor.

SELECT SERVERPROPERTY('ServerName') AS ServerName,
       SERVERPROPERTY('ProductVersion') AS EngineVersion;
SELECT name,compatibility_level FROM sys.databases WHERE database_id=DB_ID();

I check the oldest supported target before changing a shared helper script. A shorter expression is convenient only when every intended destination understands it. Keep an earlier syntax version where backward compatibility remains a real requirement. These conveniences do not justify an unrelated production compatibility change. Review the individual feature's requirements rather than borrowing a setting from a different new feature.

Read Today's Date With CURRENT_DATE

CURRENT_DATE returns a date value derived from the database server's system date. It has no parentheses and no arguments. The earlier expression casts GETDATE to date. Both describe the server date, not an automatically selected customer timezone. That distinction matters when the application operates across midnight in several regions.

SELECT CURRENT_DATE AS CurrentServerDate,
       CAST(GETDATE() AS date) AS EarlierExpression,
       CAST(SYSUTCDATETIME() AS date) AS CurrentUtcDate;

Do not label the server date UTC unless the server's time basis establishes that interpretation. Use an explicit UTC expression for a UTC contract. A date can differ between two valid locations at the same instant. I settle the date boundary rule before replacing expressions in billing or reporting. Saving a cast is useful. Moving a billing day accidentally is much less charming.

Capture One CURRENT_DATE Boundary for the Whole Operation

Assign the date to a variable when several statements must share one boundary. A procedure spanning midnight should not derive a different day halfway through the work. Use a half-open timestamp range for one day, with an inclusive start and an exclusive following day. Avoid converting every indexed timestamp to date in the predicate.

DECLARE @today date=CURRENT_DATE;
DECLARE @start datetime2(0)=CONVERT(datetime2(0),@today);
DECLARE @finish datetime2(0)=DATEADD(day,1,@start);
SELECT @today AS ChosenDate,@start AS InclusiveStart,@finish AS ExclusiveFinish;

Apply those variables to the application's intended timestamp basis. A local day over daylight saving changes needs timezone aware boundary construction before comparison with UTC instants. CURRENT_DATE answers which date the server sees. It does not eliminate the need to define what your report calls a day or which offset belongs to that day.

Concatenate With the Double Pipe Operator

The double pipe operator joins string expressions. It returns NULL when an operand is NULL. That behavior is deliberate and differs from CONCAT, which treats NULL arguments as empty strings. The sample casts the missing value to a clear string type so the comparison focuses on missing data rather than ambiguous type inference.

DECLARE @first nvarchar(30)=N'Avery',@last nvarchar(30)=NULL;
SELECT @first || N' ' || @last AS PipeResult,
       @first + N' ' + @last AS PlusResult,
       CONCAT(@first,N' ',@last) AS ConcatResult,
       @first || N' ' || COALESCE(@last,N'(missing)') AS ExplicitFallback;

CONCAT_NULL_YIELDS_NULL is on by default, so plus concatenation also propagates NULL in a normal session. The new operator's NULL rule does not depend on that session setting. With the setting switched off in a test session, plus returned the first string while the double pipe still returned NULL. Choose whether missing input should erase the combined result, become an empty piece, or display an explicit marker. A string operator cannot decide which outcome accurately represents the business value.

Keep Types and Lengths Explicit

Concatenation follows data type and length rules. Convert numeric values deliberately before combining them with text. A nonlarge string expression can truncate a wide intermediate result. Cast an operand to nvarchar(max) when the result contract genuinely requires a large Unicode value. Keep that cast early enough that an earlier concatenation has not already lost characters.

DECLARE @order_id bigint=1234567890123;
SELECT N'Order ' || CONVERT(nvarchar(30),@order_id) AS OrderLabel;
DECLARE @piece nvarchar(3000)=REPLICATE(N'x',3000);
SELECT DATALENGTH(@piece || @piece) AS FixedExpressionBytes,
       DATALENGTH(CONVERT(nvarchar(max),@piece) || @piece) AS LargeExpressionBytes;

Compare the expression lengths on your server. The input sizes are demonstration choices, not measured production data. Do not use a large type everywhere solely to avoid understanding length. Match the output contract, parameter size, and storage column. A generated label that looks correct in a short sample still needs a boundary test for the longest accepted input.

Take a Substring Through the End

SQL Server 2025 allows SUBSTRING without its length argument. It returns the substring from the chosen start through the end. An explicitly supplied NULL length is different and returns NULL. Start positions and character counting still follow the function's rules and collation behavior. A shorter call does not mean positions become zero based.

DECLARE @value nvarchar(40)=N'CODE:AB-1234  ';
SELECT SUBSTRING(@value,6) AS ThroughTheEnd,
       SUBSTRING(@value,6,DATALENGTH(@value)/2) AS EarlierExpression,
       SUBSTRING(@value,6,NULL) AS ExplicitNullLength;

The earlier expression uses a sufficiently large UTF-16 storage unit count for this demonstration so trailing spaces remain available. LEN ignores those spaces and can be unsuitable for constructing a tail length. Inspect values and byte lengths when testing. Which consumers rely on preserving trailing characters exactly? Include those consumers in the change rather than checking only the visible text in a grid.

Add a bigint Offset Without Inventing Infinite Dates

DATEADD accepts a bigint number in SQL Server 2025 and later. The larger number removes the earlier int argument limit. The destination type still has its own valid date range. Adding several billion seconds can fit a datetime2 value, while adding the same count of days cannot. Argument capacity and result capacity are different limits.

DECLARE @base datetime2(0)='2020-01-01T00:00:00';
DECLARE @seconds bigint=3000000000;
SELECT DATEADD(second,@seconds,@base) AS LaterInstant;
BEGIN TRY
    SELECT DATEADD(day,@seconds,@base) AS OutsideDateRange;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

Before this support, large additions required bounded chunks or another deliberate calculation. Avoid replacing that logic without testing its rounding and overflow behavior. The datepart remains important: months follow calendar adjustment rules, while seconds describe a different operation. Preserve those semantics when simplifying older code that already handles a boundary case correctly.

Rehearse the Semantics Along With the Syntax

Test NULLs, long strings, trailing spaces, timezone boundaries, and large offsets. Compare the intended earlier expressions on the same inputs. Save actual results from your own server for the deployment review. The examples provide operations to inspect, not invented performance gains. These changes primarily improve expression clarity and convenience.

I adopt the shorter forms when their meaning is equally clear to the team. CURRENT_DATE makes a server date direct, double pipes express string combination, and optional length makes a tail extraction readable. A bigint DATEADD expands an argument range without changing the calendar's limits. Use each improvement with a stated type and boundary contract.

Related reading on this blog: GREATEST, LEAST and Other Small Helpers and Date Boundaries With DATETRUNC and EOMONTH in SQL Server 2022.

Shorter syntax is not fewer decisions, it is a clearer expression of decisions already made.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.