How to Get the First Day and Last Day of the Month in SQL

Learn how to easily retrieve the first and last day of the month in SQL using DATE_TRUNC and interval arithmetic.

350 views

To get the first day of the month in SQL: `SELECT DATE_TRUNC('month', current_date);`. For the last day of the month: `SELECT DATE_TRUNC('month', current_date) + INTERVAL '1 month' - INTERVAL '1 day';`.

FAQs & Answers

  1. How do I find the first day of the current month in SQL? You can use the DATE_TRUNC function as follows: SELECT DATE_TRUNC('month', current_date); which returns the first day of the current month.
  2. What is the easiest way to get the last day of the month in SQL? Use DATE_TRUNC with interval arithmetic: SELECT DATE_TRUNC('month', current_date) + INTERVAL '1 month' - INTERVAL '1 day'; to get the last day of the current month.
  3. Can I use these SQL methods in all database systems? DATE_TRUNC and interval syntax are commonly supported in PostgreSQL and some other systems, but syntax may vary. Always check your specific SQL database documentation.

Watch More

For more SQL date function tutorials and advanced query tips, explore our related videos on date manipulation, aggregation, and temporal data handling to enhance your database skills.

  1. Mastering SQL: How to Find the First Day of Any Month Learn how to easily find the first day of the month in SQL with a simple query. Perfect for database management beginners.
  2. How to Easily Calculate the Last Day of the Month in SQL Learn how to use SQL's LAST_DAY function to determine the last day of any month with simple examples.
  3. How to Get the Maximum Day of the Month in SQL Using LAST_DAY and DAY Functions Learn how to find the maximum day of the current month in SQL with LAST_DAY and DAY functions for efficient date calculations.
  4. How to Calculate Last Month's Data in SQL Using DATEADD and BETWEEN Learn how to calculate last month's data in SQL with DATEADD and BETWEEN clauses for effective date filtering in queries.
  5. How to Easily Calculate the Last Day of the Month in Oracle SQL Learn how to use the LAST_DAY function in Oracle SQL to find the last day of a month for any date.