The first day of the month in SQL is a common date calculation used for monthly reporting, billing cycles, cohort analysis, and filtering records by calendar month. Because SQL is implemented differently across database systems, there is no single universal function for returning the first day of a month. Here's the thing — instead, you use date truncation, date arithmetic, or string formatting depending on whether you are working in SQL Server, MySQL, PostgreSQL, Oracle, SQLite, BigQuery, or another engine. The core idea is always the same: take any date or datetime value and reduce it to the first calendar day of its month.
Why Calculating the First Day of the Month Matters
Monthly boundaries are fundamental in analytical SQL. A business may need to answer questions such as:
- How many orders were placed in the current month?
- What was the revenue for each month this year?
- Which customers signed up in the same month?
- How many subscriptions renew on the first day of each month?
- What records fall between the start and end of a reporting period?
Using the first day of the month as a standardized boundary makes these queries easier to write and compare. It also helps avoid off-by-one errors that occur when dates include time components. A datetime value such as 2024-05-17 14:32:00 belongs to May, but filtering with `BETWEEN '202
Practical Techniques Across Major Platforms
Below are the most common patterns you’ll encounter, along with ready‑to‑copy snippets you can drop into a query. All of them assume a column named order_date (or any datetime column) that you want to normalize to the first day of its month.
SQL Server (T‑SQL)
-- Method 1: Using DATEADD
SELECT DATEADD(month, DATEDIFF(month, 0, order_date), 0) AS first_day_of_month
FROM sales;
-- Method 2: Using the built‑in EOMONTH trick (SQL Server 2012+)
SELECT CAST(FORMAT(order_date, 'yyyy-MM-01') AS date) AS first_day_of_month
FROM sales;
Why it works: DATEDIFF(month, 0, order_date) returns the number of month boundaries crossed from the “zero” date (1900‑01‑01). Adding that many months to 0 lands on the first day of the target month.
MySQL / MariaDB
-- Using DATE_FORMAT
SELECT DATE_FORMAT(order_date, '%Y-%m-01') AS first_day_of_month
FROM sales;
-- Alternative: DATE_SUB + INTERVAL
SELECT DATE_SUB(order_date, INTERVAL (DAY(order_date) - 1) DAY) AS first_day_of_month
FROM sales;
Why it works: DATE_FORMAT forces the day portion to 01. The second approach subtracts the current day‑of‑month minus one, rolling back to the month start Surprisingly effective..
PostgreSQL
-- Using DATE_TRUNC (available in PostgreSQL 9.4+)
SELECT DATE_TRUNC('month', order_date) AS first_day_of_month
FROM sales;
-- Using to_char and casting
SELECT (ORDER_DATE::date) - (EXTRACT(DAY FROM order_date) - 1) AS first_day_of_month
FROM sales;
DATE_TRUNC already returns the first day at midnight. The arithmetic version manually backs up to the start of the month That's the part that actually makes a difference..
Oracle
-- Using TRUNC
SELECT TRUNC(order_date, 'MM') AS first_day_of_month
FROM sales;
-- Using ADD_MONTHS for the first day of the next month, then subtract one day
SELECT ADD_MONTHS(TRUNC(order_date, 'Q'), -3) -- example placeholder
TRUNC with 'MM' discards the day and sets it to 1, preserving the time component (if you need a timestamp, you can also use TRUNC(order_date, 'MM')).
SQLite
SELECT strftime('%Y-%m-01', order_date) AS first_day_of_month
FROM sales;
SQLite’s strftime works like a simplified DATE_FORMAT. If you need a date value rather than a string, cast the result: CAST(strftime(...) AS DATE).
BigQuery
SELECT DATE_TRUNC(order_date, MONTH) AS first_day_of_month
FROM `project.dataset.sales`;
BigQuery’s DATE_TRUNC mirrors PostgreSQL’s behavior and returns a DATE type (time component dropped).
Snowflake
SELECT DATE_TRUNC('MONTH', order_date) AS first_day_of_month
FROM sales;
Snowflake also supports TO_DATE(TO_CHAR(order_date, 'YYYY-MM-01')) if you prefer a string‑based route.
Choosing the Right Approach
| Requirement | Recommended Function | Reason |
|---|---|---|
| Standard SQL (ANSI) | DATE_TRUNC('month', col) (supported by many modern DBs) |
Consistent behavior across platforms |
| Time‑zone‑aware calculations | Use the DB’s built‑in date functions that respect session TZ (e.g., TIMESTAMP truncation in PostgreSQL) |
Avoids midnight‑shift surprises |
| Performance on large tables | Pre‑compute the first‑day values in a CTE or materialized view if the expression is expensive | Reduces repeated CPU cycles |
| ** |
Performance on large tables
When working with large datasets, the efficiency of date calculations can impact query performance. Some databases benefit from pre-computing the first-day-of-month values in a Common Table Expression (CTE) or materialized view, especially if the expression is used repeatedly in joins or aggregations. This approach minimizes redundant computations and can put to work indexing more effectively.
Handling Time Zones
If your application deals with multiple time zones, be cautious with functions that truncate to the month. In databases like PostgreSQL, DATE_TRUNC operates on the session's time zone. For consistent results, consider converting to a specific time zone first (e.g., using AT TIME ZONE) to avoid unexpected shifts around midnight.
General Recommendations
- Readability: Prefer built-in functions like
DATE_TRUNC(PostgreSQL, BigQuery, Snowflake) orTRUNC(Oracle) for clarity. - Consistency: When targeting multiple databases, use ANSI SQL-compliant methods or abstract the logic into a view or function to maintain portability.
- Testing: Always test with edge cases, such as dates near the end of the month or during daylight saving time transitions, to ensure correctness.
The short version: obtaining the first day of the month is a common task with solutions designed for each database system. In practice, by understanding the available functions and their implications—especially regarding performance and time zones—you can write queries that are both efficient and reliable. Choose the method that aligns with your database's strengths and your project's requirements to achieve the best results.