Introduction
When working with date‑time data in SQL, you often need to isolate a specific part of a date, such as the first day of a month. Plus, the ability to get the first day of the month is essential for reporting, data aggregation, and time‑based filtering. In practice, this article explains how to retrieve the first day of any month using standard SQL functions, with practical examples for popular database platforms like MySQL, PostgreSQL, SQL Server, and Oracle. By the end, you’ll have a clear understanding of the most reliable techniques and be able to apply them confidently in your queries.
Steps to Retrieve the First Day of the Month
Below are step‑by‑step methods for each major SQL dialect. Choose the one that matches your database system.
MySQL / MariaDB
MySQL provides several ways to manipulate dates. The most straightforward approach is to use DATE_FORMAT() combined with STR_TO_DATE() or the DATE() function Small thing, real impact..
-
Using
DATE_FORMAT()SELECT DATE_FORMAT(CURRENT_DATE, '%Y-%m-01');This returns the first day of the current month. To apply it to a column, replace
CURRENT_DATEwith your date column Most people skip this — try not to.. -
Using
STR_TO_DATE()SELECT STR_TO_DATE(DATE_FORMAT(order_date, '%Y-%m'), '%Y-%m-01');This extracts the year‑month from
order_dateand builds the first day of that month Easy to understand, harder to ignore. Which is the point.. -
Using
DATE()withCONCAT()SELECT DATE(CONCAT(YEAR(order_date), '-', MONTH(order_date), '-01'));This method is useful when you need to generate a series of month‑start dates dynamically.
PostgreSQL
PostgreSQL offers the DATE_TRUNC() function, which is ideal for truncating a timestamp to a specific precision Easy to understand, harder to ignore..
-
Truncating to month
SELECT DATE_TRUNC('month', CURRENT_DATE);This returns the first day of the current month at 00:00:00 Most people skip this — try not to..
-
Applying to a column
SELECT DATE_TRUNC('month', order_date) AS month_start FROM sales WHERE DATE_TRUNC('month', order_date) = DATE_TRUNC('month', CURRENT_DATE);This filters records to those belonging to the current month Which is the point..
-
Using
MAKE_DATE()for custom logicSELECT MAKE_DATE(EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date), 1);This constructs a date using year, month, and day components, ensuring the day is always
1.
SQL Server
SQL Server uses DATEADD() and DATEDIFF() functions to manipulate dates.
-
Basic approach
SELECT DATEADD(day, 1 - DAY(GETDATE()), GETDATE());DAY(GETDATE())returns the day of the month (1‑31). SubtractingDAY‑1from the date moves the date back to the first day of the month. -
Using
FORMAT()for readabilitySELECT FORMAT(DATEADD(day, 1 - DAY(order_date), order_date), 'yyyy-MM-dd') AS month_start FROM orders; -
Creating a month‑start series
WITH months AS ( SELECT FORMAT(DATEADD(month, n, '2000-01-01'), 'yyyy-MM-01') AS month_start FROM master..spt_values WHERE n BETWEEN 0 AND 11 ) SELECT * FROM months;
Oracle
Oracle provides ADD_MONTHS and TRUNC functions, which can be combined to get the first day of the month.
-
Using
TRUNC()SELECT TRUNC(SYSDATE, 'MM') FROM dual;TRUNCwith the'MM'unit returns the first day of the month. -
Using
ADD_MONTHSSELECT ADD_MONTHS(TRUNC(order_date, 'MM'), 0) AS month_start FROM orders;This ensures the date is already the first day of its month Surprisingly effective..
-
Dynamic month generation
SELECT TO_DATE(TO_CHAR(order_date, 'YYYY-MM') || '-01', 'YYYY-MM-DD') FROM orders;
Scientific Explanation
Understanding how dates are stored and manipulated helps you choose the most efficient method.
-
Date Storage: Most relational databases store dates as numeric values (e.g., days since an epoch) or as fixed‑length strings. Operations like addition, subtraction, and truncation rely on these underlying representations.
-
Truncation Logic: Functions such as
DATE_TRUNC('month', ...)internally compute the year and month components, then reset the day component to1. This is mathematically equivalent toDATEADD(day, 1 - DAY(date), date)But it adds up.. -
Time Zone Considerations: When dealing with timestamps that include time components, truncating to month often discards the time portion, returning a timestamp at
00:00:00. Be aware of time‑zone settings, especially in distributed systems. -
Performance Impact: Using functions directly on indexed columns can prevent index usage, leading to full table scans. If possible, pre‑compute month‑start values or store them in a separate column to improve query performance Nothing fancy..
Frequently Asked Questions (FAQ)
What if I need the first day of the previous month?
You can subtract one month from the result of the month‑start calculation. Here's one way to look at it: in PostgreSQL:
SELECT DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month');
In SQL Server:
SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0);
Can I use a variable to store the month‑start date?
Yes. Most databases allow declaring variables or using CTEs. Example in MySQL:
SET @first_day := DATE_FORMAT(CURRENT_DATE, '%Y-%m-01');
SELECT @first_day;
How do I handle NULL dates?
Most date functions return NULL when the input is NULL. If you need to replace NULL with a default month‑start, use COALESCE:
SELECT COALESCE(DATE_TRUNC('month', order_date), DATE '1970-01-01') AS month_start
FROM orders;
Is there a way to generate a series of month‑start dates?
Yes. You can use recursive CTEs (
common table expressions) to build a calendar of month boundaries on the fly. In PostgreSQL:
WITH RECURSIVE months AS (
SELECT DATE_TRUNC