Sql Get First Day Of Month

5 min read

Finding the first day of the month is a fundamental requirement in SQL reporting, data warehousing, and application logic. Worth adding: whether you are partitioning data by month, calculating monthly aggregates, or building a dynamic date filter for a dashboard, the ability to anchor a timestamp to the start of its calendar month is essential. Because SQL dialects handle date arithmetic differently, the syntax varies significantly between PostgreSQL, MySQL, SQL Server, Oracle, and Snowflake. This guide provides the standard approaches for every major platform, explains the logic behind the functions, and offers performance-focused best practices for production environments And that's really what it comes down to..

Understanding the Core Logic

Before diving into vendor-specific syntax, it helps to understand the two primary algorithms databases use to calculate this value.

1. Truncation (Rounding Down) This is the most intuitive method. The database takes a specific timestamp—like 2023-10-15 14:30:00—and "rounds it down" to the nearest month boundary, resulting in 2023-10-01 00:00:00. Functions like DATE_TRUNC (PostgreSQL, Snowflake, Redshift) or TRUNC (Oracle) operate this way.

2. Arithmetic Subtraction Older engines or those lacking a dedicated truncation function rely on math. The logic extracts the day component of the date (e.g., 15), subtracts 1 (resulting in 14), and subtracts that interval from the original date. Date - (Day(Date) - 1) Days = First Day of Month. This approach is common in SQL Server (using DATEADD/DATEDIFF) and MySQL (using DATE_SUB/DAY) Surprisingly effective..

Dialect-Specific Implementations

PostgreSQL, Snowflake, Redshift, BigQuery, Spark SQL

These modern analytical databases support the ANSI-adjacent DATE_TRUNC function. It is concise, readable, and highly performant Simple, but easy to overlook..

-- Returns a timestamp at 00:00:00.000
SELECT DATE_TRUNC('month', CURRENT_DATE) AS first_day_of_month;

-- If you strictly need a DATE type (without time component)
SELECT DATE_TRUNC('month', CURRENT_DATE)::DATE;

Why it works: DATE_TRUNC accepts a 'precision' argument ('month', 'quarter', 'year', 'week'). It effectively zeros out all lower-precision fields (day, hour, minute, second) Not complicated — just consistent..

MySQL and MariaDB

MySQL does not have DATE_TRUNC. The standard approach uses DATE_SUB combined with DAY() (or DAYOFMONTH()) and INTERVAL arithmetic But it adds up..

-- Standard MySQL 5.7+ / 8.0 syntax
SELECT DATE_SUB(CURRENT_DATE, INTERVAL DAY(CURRENT_DATE) - 1 DAY) AS first_day_of_month;

-- Alternative using DATE_FORMAT (returns string, requires cast for date math)
SELECT CAST(DATE_FORMAT(CURRENT_DATE, '%Y-%m-01') AS DATE);

Performance Note: The DATE_SUB arithmetic method is SARGable (Search ARGument Able), meaning it allows the optimizer to use an index on the date column if you flip the logic for a WHERE clause (see the "Filtering Data" section below). The DATE_FORMAT method applies a function to the column, often forcing a full table scan.

SQL Server (T-SQL)

SQL Server developers traditionally use the DATEADD / DATEDIFF pattern. This calculates the number of month boundaries crossed since the "zero date" (1900-01-01) and adds them back to that zero date.

-- Classic T-SQL Pattern (Works on all versions)
SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) AS first_day_of_month;

-- Modern SQL Server 2022+ / Azure SQL: DATETRUNC function
SELECT DATETRUNC(month, GETDATE()) AS first_day_of_month;

Explanation of the Classic Pattern:

  1. DATEDIFF(month, 0, GETDATE()) counts the month boundaries between 1900-01-01 and today.
  2. DATEADD(month, ..., 0) adds that count of months back to 1900-01-01, landing exactly on the first day of the current month at midnight.

Oracle Database

Oracle provides the TRUNC function with a format model. This is distinct from the TRUNCATE table command and works natively on DATE and TIMESTAMP types.

-- Returns a DATE type at midnight (00:00:00)
SELECT TRUNC(SYSDATE, 'MM') AS first_day_of_month FROM dual;

-- Valid format models for month start: 'MM', 'MONTH', 'MON'

SQLite

SQLite relies on the versatile date() function with modifiers. It treats dates as strings in 'YYYY-MM-DD' format No workaround needed..

-- 'start of month' modifier handles the logic internally
SELECT date('now', 'start of month') AS first_day_of_month;

-- Works on specific columns too
SELECT date(order_date, 'start of month') FROM orders;

DB2 (IBM)

DB2 uses the TRUNCATE (or TRUNC) scalar function, similar to Oracle but with slightly different syntax requirements for the timestamp argument.

SELECT TRUNC(CURRENT DATE, 'MONTH') FROM sysibm.sysdummy1;

Practical Application: Filtering "Current Month" Data

Probably most common mistakes developers make is writing non-SARGable WHERE clauses. Compare these two approaches for finding orders placed in the current month.

❌ The Anti-Pattern (Index Killer)

-- MySQL Example
WHERE MONTH(order_date) = MONTH(CURRENT_DATE) 
  AND YEAR(order_date) = YEAR(CURRENT_DATE);

-- SQL Server Example
WHERE MONTH(order_date) = MONTH(GETDATE()) 
  AND YEAR(order_date) = YEAR(GETDATE());

Why this fails: The database must apply the MONTH() and YEAR() functions to every single row in the orders table before it can compare the values. It cannot seek an index on order_date Worth keeping that in mind. Simple as that..

✅ The SARGable Pattern (Index Friendly)

Calculate the boundaries once as variables or literals, then use open/closed range logic It's one of those things that adds up..

-- Generic Logic (Adapt syntax per dialect)
DECLARE @StartOfMonth DATE = ;
DECLARE @StartOfNextMonth DATE = DATEADD(month, 1, @StartOfMonth); -- Or + INTERVAL 1 MONTH

SELECT * 
FROM orders 
WHERE order_date >= @StartOfMonth 
  AND order_date < @StartOfNextMonth;

Why this wins:

  1. The functions run once per query, not once per row.
  2. The optimizer sees order_date >= '2023-10-01' AND order_date < '2023-11-01'.
  3. It performs an Index Seek on order_date, touching only the relevant rows.

Handling Time Zones and Timestamps

If your column is TIMESTAMP WITH TIME ZONE (e.g., TIMESTAMPTZ in Postgres), DATE_TRUNC behavior depends on the session time zone.

-- Postgres Example
SET TIME ZONE 'UTC';
SELECT DATE_TRUNC('month', '2023-10-31 2
Latest Drops

New Writing

Similar Territory

You're Not Done Yet

Thank you for reading about Sql Get First Day Of Month. We hope the information has been useful. Feel free to contact us if you have any questions. See you next time — don't forget to bookmark!
⌂ Back to Home