SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table t (d datetime);
insert t
select top (300000) DATEADD(second, (ABS(CHECKSUM(NEWID())) % 6307200000), 0)
from master..spt_values a
cross join master..spt_values b
300000 rows affected

set statistics xml on;
DECLARE @d datetime2 = SYSDATETIME();

SELECT MAX(
  dateadd(day, 1, DATETRUNC(DAY, d))
)
FROM t;

SELECT DATEDIFF(ms, @d, SYSDATETIME());

(No column name)

1968-01-21 00:00:00.000

Microsoft SQL Server 2005 XML Showplan

<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.564" Build="16.0.1000.6"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT MAX(&#xa;  dateadd(day, 1, DATETRUNC(DAY, d))&#xa;)&#xa;FROM t" StatementId="1" StatementCompId="2" StatementType="SELECT" StatementSqlHandle="0x0900F47D9293EC0F889068F4D6175D6D64540000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" StatementParameterizationType="0" RetrievedFromCache="true" StatementSubTreeCost="1.39439" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0xABEE1B1EC6941F2C" QueryPlanHash="0x25DAE3B291468A1F" CardinalityEstimationModelVersion="160"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="16" CompileTime="359" CompileCPU="359" CompileMemory="256"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="3360912"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2023-03-25T20:10:12.08" ModificationCount="0" SamplingPercent="94.693" Statistics="[_WA_Sys_00000001_35BCFE0A]" Table="[t]" Schema="[dbo]" Database="[fiddle_0d3cd82e88ac4774afcdef7b35aaf112]"></StatisticsInfo></OptimizerStatsUsage><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="124" CpuTime="124"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="1" EstimateIO="0" EstimateCPU="0.18" AvgRowSize="15" EstimatedTotalSubtreeCost="1.39439" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1003"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualElapsedms="124" ActualCPUms="124" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><StreamAggregate><DefinedValues><DefinedValue><ColumnReference Column="Expr1003"></ColumnReference><ScalarOperator ScalarString="MAX([Expr1004])"><Aggregate Distinct="0" AggType="MAX"><ScalarOperator><Identifier><ColumnReference Column="Expr1004"></ColumnReference></Identifier></ScalarOperator></Aggregate></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="1" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="300000" EstimateIO="0" EstimateCPU="0.03" AvgRowSize="15" EstimatedTotalSubtreeCost="1.21439" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1004"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1004"></ColumnReference><ScalarOperator ScalarString="dateadd(day,(1),datetrunc(day,[fiddle_0d3cd82e88ac4774afcdef7b35aaf112].[dbo].[t].[d]))"><Intrinsic FunctionName="dateadd"><ScalarOperator><Const ConstValue="(4)"></Const></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator><ScalarOperator><Intrinsic FunctionName="datetrunc"><ScalarOperator><Const ConstValue="(4)"></Const></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_0d3cd82e88ac4774afcdef7b35aaf112]" Schema="[dbo]" Table="[t]" Column="d"></ColumnReference></Identifier></ScalarOperator></Intrinsic></ScalarOperator></Intrinsic></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="2" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="300000" EstimatedRowsRead="300000" EstimateIO="0.854236" EstimateCPU="0.330157" AvgRowSize="15" EstimatedTotalSubtreeCost="1.18439" TableCardinality="300000" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_0d3cd82e88ac4774afcdef7b35aaf112]" Schema="[dbo]" Table="[t]" Column="d"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="300000" Batches="0" ActualElapsedms="31" ActualCPUms="31" ActualScans="1" ActualLogicalReads="1150" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="300000" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><TableScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_0d3cd82e88ac4774afcdef7b35aaf112]" Schema="[dbo]" Table="[t]" Column="d"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_0d3cd82e88ac4774afcdef7b35aaf112]" Schema="[dbo]" Table="[t]" IndexKind="Heap" Storage="RowStore"></Object></TableScan></RelOp></ComputeScalar></RelOp></StreamAggregate></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>

(No column name)

125

DECLARE @d datetime2 = SYSDATETIME();

SELECT MAX(
  DATETRUNC(DAY, dateadd(day, 1, d))
)
FROM t;

SELECT DATEDIFF(ms, @d, SYSDATETIME());

(No column name)

1968-01-21 00:00:00.000

Microsoft SQL Server 2005 XML Showplan

<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.564" Build="16.0.1000.6"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT MAX(&#xa;  DATETRUNC(DAY, dateadd(day, 1, d))&#xa;)&#xa;FROM t" StatementId="1" StatementCompId="2" StatementType="SELECT" StatementSqlHandle="0x090009A1BD4F3E7B1F1C375A279C32D8ED0E0000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" StatementParameterizationType="0" RetrievedFromCache="true" StatementSubTreeCost="1.39439" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0xACF16D910B2592C6" QueryPlanHash="0x25DAE3B291468A1F" CardinalityEstimationModelVersion="160"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="16" CompileTime="0" CompileCPU="0" CompileMemory="256"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="3360912"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2023-03-25T20:10:12.08" ModificationCount="0" SamplingPercent="94.693" Statistics="[_WA_Sys_00000001_35BCFE0A]" Table="[t]" Schema="[dbo]" Database="[fiddle_0d3cd82e88ac4774afcdef7b35aaf112]"></StatisticsInfo></OptimizerStatsUsage><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="124" CpuTime="124"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="1" EstimateIO="0" EstimateCPU="0.18" AvgRowSize="15" EstimatedTotalSubtreeCost="1.39439" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1003"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualElapsedms="124" ActualCPUms="124" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><StreamAggregate><DefinedValues><DefinedValue><ColumnReference Column="Expr1003"></ColumnReference><ScalarOperator ScalarString="MAX([Expr1004])"><Aggregate Distinct="0" AggType="MAX"><ScalarOperator><Identifier><ColumnReference Column="Expr1004"></ColumnReference></Identifier></ScalarOperator></Aggregate></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="1" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="300000" EstimateIO="0" EstimateCPU="0.03" AvgRowSize="15" EstimatedTotalSubtreeCost="1.21439" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1004"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1004"></ColumnReference><ScalarOperator ScalarString="datetrunc(day,dateadd(day,(1),[fiddle_0d3cd82e88ac4774afcdef7b35aaf112].[dbo].[t].[d]))"><Intrinsic FunctionName="datetrunc"><ScalarOperator><Const ConstValue="(4)"></Const></ScalarOperator><ScalarOperator><Intrinsic FunctionName="dateadd"><ScalarOperator><Const ConstValue="(4)"></Const></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_0d3cd82e88ac4774afcdef7b35aaf112]" Schema="[dbo]" Table="[t]" Column="d"></ColumnReference></Identifier></ScalarOperator></Intrinsic></ScalarOperator></Intrinsic></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="2" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="300000" EstimatedRowsRead="300000" EstimateIO="0.854236" EstimateCPU="0.330157" AvgRowSize="15" EstimatedTotalSubtreeCost="1.18439" TableCardinality="300000" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_0d3cd82e88ac4774afcdef7b35aaf112]" Schema="[dbo]" Table="[t]" Column="d"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="300000" Batches="0" ActualElapsedms="62" ActualCPUms="62" ActualScans="1" ActualLogicalReads="1150" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="300000" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><TableScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_0d3cd82e88ac4774afcdef7b35aaf112]" Schema="[dbo]" Table="[t]" Column="d"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_0d3cd82e88ac4774afcdef7b35aaf112]" Schema="[dbo]" Table="[t]" IndexKind="Heap" Storage="RowStore"></Object></TableScan></RelOp></ComputeScalar></RelOp></StreamAggregate></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>

(No column name)

125

DECLARE @d datetime2 = SYSDATETIME();

SELECT MAX(
  dateadd(day, 1, CAST(d AS date))
)
FROM t;

SELECT DATEDIFF(ms, @d, SYSDATETIME());

(No column name)

1968-01-21

Microsoft SQL Server 2005 XML Showplan

<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.564" Build="16.0.1000.6"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT MAX(&#xa;  dateadd(day, 1, CAST(d AS date))&#xa;)&#xa;FROM t" StatementId="1" StatementCompId="2" StatementType="SELECT" StatementSqlHandle="0x0900C4024FAE63C43CD81C3A9377B3A7526F0000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" StatementParameterizationType="0" RetrievedFromCache="true" StatementSubTreeCost="1.39439" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x982D827A1337F397" QueryPlanHash="0x25DAE3B291468A1F" CardinalityEstimationModelVersion="160"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="16" CompileTime="0" CompileCPU="0" CompileMemory="264"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="3360912"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2023-03-25T20:10:12.08" ModificationCount="0" SamplingPercent="94.693" Statistics="[_WA_Sys_00000001_35BCFE0A]" Table="[t]" Schema="[dbo]" Database="[fiddle_0d3cd82e88ac4774afcdef7b35aaf112]"></StatisticsInfo></OptimizerStatsUsage><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="109" CpuTime="109"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="1" EstimateIO="0" EstimateCPU="0.18" AvgRowSize="10" EstimatedTotalSubtreeCost="1.39439" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1003"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualElapsedms="109" ActualCPUms="109" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><StreamAggregate><DefinedValues><DefinedValue><ColumnReference Column="Expr1003"></ColumnReference><ScalarOperator ScalarString="MAX([Expr1004])"><Aggregate Distinct="0" AggType="MAX"><ScalarOperator><Identifier><ColumnReference Column="Expr1004"></ColumnReference></Identifier></ScalarOperator></Aggregate></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="1" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="300000" EstimateIO="0" EstimateCPU="0.03" AvgRowSize="10" EstimatedTotalSubtreeCost="1.21439" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1004"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1004"></ColumnReference><ScalarOperator ScalarString="dateadd(day,(1),CONVERT(date,[fiddle_0d3cd82e88ac4774afcdef7b35aaf112].[dbo].[t].[d],0))"><Intrinsic FunctionName="dateadd"><ScalarOperator><Const ConstValue="(4)"></Const></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator><ScalarOperator><Convert DataType="date" Style="0" Implicit="0"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_0d3cd82e88ac4774afcdef7b35aaf112]" Schema="[dbo]" Table="[t]" Column="d"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></Intrinsic></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="2" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="300000" EstimatedRowsRead="300000" EstimateIO="0.854236" EstimateCPU="0.330157" AvgRowSize="15" EstimatedTotalSubtreeCost="1.18439" TableCardinality="300000" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_0d3cd82e88ac4774afcdef7b35aaf112]" Schema="[dbo]" Table="[t]" Column="d"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="300000" Batches="0" ActualElapsedms="15" ActualCPUms="15" ActualScans="1" ActualLogicalReads="1150" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="300000" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><TableScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_0d3cd82e88ac4774afcdef7b35aaf112]" Schema="[dbo]" Table="[t]" Column="d"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_0d3cd82e88ac4774afcdef7b35aaf112]" Schema="[dbo]" Table="[t]" IndexKind="Heap" Storage="RowStore"></Object></TableScan></RelOp></ComputeScalar></RelOp></StreamAggregate></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>

(No column name)

109