Get the First Day of the Current Month

Month-start boundaries show up everywhere in reporting queries, billing cycles, cohort aggregations, and partition pruning. Hard-coding a date string works for one run but quickly rots. Deriving the first day of the current month from GETDATE() keeps the query portable across environments.

In SQL Server, subtract the current day-of-month and add one day back to land on the first:

SELECT CONVERT(VARCHAR(25),
       DATEADD(DAY, -(DAY(GETDATE())) + 1, GETDATE()),
       105) AS Day1;

Modern alternatives (2022+ SQL Server)

Newer versions of SQL Server ship DATETRUNC, which makes this a one-liner:

SELECT DATETRUNC(month, GETDATE()) AS FirstDayOfMonth;

For cross-engine work, the equivalents are DATE_TRUNC('month', CURRENT_DATE) in PostgreSQL, DATE_FORMAT(CURDATE(), '%Y-%m-01') in MySQL, and TRUNC(SYSDATE, 'MM') in Oracle. Pick the one that matches your dialect and stop re-deriving the formula in every report.

Charlie Frank

Charlie has over a decade of experience in website administration and technology management. As the site admin, he oversees all technical aspects of running a high-traffic online platform, ensuring optimal performance, security, and user experience.