SQL Server First Day of Month: DATETRUNC vs DATEFROMPARTS Tested

On SQL Server 2022 and later, the SQL Server first day of month is one call, DATETRUNC(month, @d), and it keeps your input type. The older tricks don’t.

Quick answer: line one on 2022+, line two on 2012 through 2019 (returns date).

SELECT DATETRUNC(month, @d);
SELECT DATEFROMPARTS(YEAR(@d), MONTH(@d), 1);

Tested on SQL Server 2025 Developer (17.0.1000.7), sqlcmd in VS Code, fixed dates.

How I Tested Five First-Day-of-Month Methods

The test table: 120,000 orders, one every four minutes from 01/01/2026, indexed on OrderDate (GENERATE_SERIES needs 2022+):

CREATE TABLE dbo.Orders (
    OrderId   int IDENTITY PRIMARY KEY,
    OrderDate datetime2(0)  NOT NULL,
    Amount    decimal(10,2) NOT NULL);

INSERT INTO dbo.Orders (OrderDate, Amount)
SELECT DATEADD(minute, value * 4, '2026-01-01'),
       10 + (value % 9000) / 100.0
FROM GENERATE_SERIES(1, 120000);

CREATE INDEX IX_Orders_OrderDate ON dbo.Orders (OrderDate) INCLUDE (Amount);

All five methods, fed 03/17/2026 2:25 PM:

SET NOCOUNT ON;
DECLARE @d datetime2(0) = '2026-03-17 14:25:09';
SELECT m.Method, CAST(m.v AS varchar(19)) AS FirstDay,
       CAST(SQL_VARIANT_PROPERTY(m.v, 'BaseType') AS varchar(9)) AS Type
FROM (VALUES
  ('DATETRUNC',     CAST(DATETRUNC(month, @d) AS sql_variant)),
  ('DATEFROMPARTS', DATEFROMPARTS(YEAR(@d), MONTH(@d), 1)),
  ('EOMONTH',       DATEADD(day, 1, EOMONTH(@d, -1))),
  ('DATEDIFF',      DATEADD(month, DATEDIFF(month, 0, @d), 0)),
  ('String',        CONVERT(char(7), @d, 120) + '-01')) AS m(Method, v);

What came back:

Method        FirstDay            Type     
------------- ------------------- ---------
DATETRUNC     Mar  1 2026 12:00AM datetime2
DATEFROMPARTS Mar  1 2026         date     
EOMONTH       Mar  1 2026         date     
DATEDIFF      Mar  1 2026 12:00AM datetime 
String        2026-03-01          varchar

Same March 1, four types. DATETRUNC kept datetime2; DATEDIFF downgraded to datetime; the string isn’t a date.

Want a date? Convert the datetime to date first.

First Day of Previous Month, Next Month, Quarter and Year

Shift with DATEADD, then truncate:

SET NOCOUNT ON;
DECLARE @d date = '2026-03-17';
SELECT DATETRUNC(month, DATEADD(month, -1, @d))  AS PrevMonth,
       DATEADD(month, 1, DATETRUNC(month, @d))   AS NextMonth,
       DATEADD(month, -12, DATETRUNC(month, @d)) AS YearAgo,
       DATETRUNC(quarter, @d)                    AS QuarterStart,
       DATETRUNC(year, @d)                       AS YearStart;

For 03/17/2026:

PrevMonth        NextMonth        YearAgo          QuarterStart     YearStart       
---------------- ---------------- ---------------- ---------------- ----------------
      2026-02-01       2026-04-01       2025-03-01       2026-01-01       2026-01-01

For the current month, pass GETDATE(). The month’s other end: SQL Server last day of month.

Which Month Filter Can Use an Index?

Here the method matters. I summed March three ways; each returned $609,856.20:

SET NOCOUNT ON;
DECLARE @m date = '2026-03-01';
SELECT /*trunc*/ SUM(Amount) FROM dbo.Orders
WHERE DATETRUNC(month, OrderDate) = @m;
GO
DECLARE @m date = '2026-03-01';
SELECT /*yrmon*/ SUM(Amount) FROM dbo.Orders
WHERE YEAR(OrderDate) = YEAR(@m) AND MONTH(OrderDate) = MONTH(@m);
GO
DECLARE @m date = '2026-03-01';
SELECT /*range*/ SUM(Amount) FROM dbo.Orders
WHERE OrderDate >= @m AND OrderDate < DATEADD(month, 1, @m);
GO

Then I pulled logical reads and the plan operator from Query Store. The grant hint matters: my first runs sat in RESOURCE_SEMAPHORE for up to 141 seconds on a busy instance.

SET NOCOUNT ON;
SELECT CAST(SUBSTRING(t.query_sql_text, CHARINDEX('/*', t.query_sql_text) + 2, 5)
            AS char(5)) AS Filter,
       r.last_logical_io_reads AS Reads,
       IIF(p.query_plan LIKE '%PhysicalOp="Index Seek"%', 'Seek', 'Scan') AS Op
FROM sys.query_store_query_text AS t
JOIN sys.query_store_query AS q ON q.query_text_id = t.query_text_id
JOIN sys.query_store_plan AS p ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS r ON r.plan_id = p.plan_id
WHERE t.query_sql_text LIKE '%SELECT /*[a-z]%'
ORDER BY Reads
OPTION (MAX_GRANT_PERCENT = 1);

Reads:

Filter Reads                Op  
------ -------------------- ----
range                    38 Seek
trunc                   374 Scan
yrmon                   374 Scan

A function on OrderDate forced a scan: 374 reads for DATETRUNC, 374 for YEAR/MONTH. The range seeked in 38. Ten times fewer.

For month-to-date, end the range at DATEADD(day, 1, @asOf).

Group by Month With DATETRUNC

Grouping is where DATETRUNC on the column is fine:

SET NOCOUNT ON;
SELECT DATETRUNC(month, OrderDate) AS MonthStart, COUNT(*) AS Orders,
       CAST(SUM(Amount) AS decimal(10,2)) AS Revenue
FROM dbo.Orders
WHERE OrderDate >= '2026-01-01' AND OrderDate < '2026-04-01'
GROUP BY DATETRUNC(month, OrderDate)
ORDER BY MonthStart;

Monthly totals:

MonthStart                             Orders      Revenue     
-------------------------------------- ----------- ------------
                   2026-01-01 00:00:00       11159    539862.20
                   2026-02-01 00:00:00       10080    534909.60
                   2026-03-01 00:00:00       11160    609856.20

March matches the filter total. Need “March” instead of 2026-03-01? See how to get the month name from a date.

First Monday of the Month Depends on DATEFIRST

The usual DATEPART(weekday) formula assumes DATEFIRST 7. Here it is under DATEFIRST 1, which SET LANGUAGE can switch on for you:

SET NOCOUNT ON;
DECLARE @m date = '2026-03-01';
SET DATEFIRST 1;
SELECT @@DATEFIRST AS DF,
       DATEADD(day, (9 - DATEPART(weekday, @m)) % 7, @m) AS Naive,
       DATEADD(day, (7 - DATEDIFF(day, '19000101', @m) % 7) % 7, @m) AS Safe;

Output:

DF  Naive            Safe            
--- ---------------- ----------------
  1       2026-03-03       2026-03-02

March 1, 2026 is a Sunday, so the answer is March 2. Naive says Tuesday the 3rd.

Safe counts days from 01/01/1900, a Monday. See SQL Server datetime functions.

Common First Day of Month Errors and Fixes

Two traps: DATEFORMAT dmy, and MONTH() + 1 in December.

SET NOCOUNT ON;
DECLARE @s varchar(10) = CONVERT(char(7), CAST('2026-03-17' AS date), 120) + '-01';
SET DATEFORMAT dmy;
SELECT CAST(@s AS datetime) AS AsDatetime, CAST(@s AS date) AS AsDate;

DECLARE @dec date = '2026-12-15';
BEGIN TRY SELECT DATEFROMPARTS(YEAR(@dec), MONTH(@dec) + 1, 1); END TRY
BEGIN CATCH PRINT CONCAT('Msg ', ERROR_NUMBER(), ': ', ERROR_MESSAGE()); END CATCH;

Output:

AsDatetime              AsDate          
----------------------- ----------------
2026-01-03 00:00:00.000       2026-03-01
                
----------------
Msg 289: Cannot construct data type date, some of the arguments have values which are not valid.

The datetime cast read 2026-03-01 as January 3 with no error; the date type ignored DATEFORMAT. Converting strings to dates lists the safe formats.

Msg 289 means month 13. Add months with DATEADD, then truncate.

Where the First-Day-of-Month Verdict Flips

My rule of thumb: DATETRUNC in SELECT and GROUP BY, a bare date range in WHERE. On 2019 or older the answer flips to DATEFROMPARTS. Re-test after upgrades.

Next step: format the date for display. PostgreSQL readers want PostgreSQL date_trunc.

Two more worth a bookmark:

• The CONVERT function and its style codes
• datetime vs datetime2

Frequently Asked Questions