SQL Server First Date Of Month

by

Knowing how to efficiently retrieve the first day of a month in SQL Server is an essential skill for a developer. In this comprehensive article, I’ll walk you through multiple approaches and explain when to use each approach, providing examples along the way.

SQL Server First Date Of Month

Before diving into the technical solutions, let’s consider some common scenarios where you’d need to find the first day of a month:

  • Creating monthly financial reports
  • Setting up date range filters
  • Establishing billing cycles
  • Scheduling recurring tasks or events

Now let’s explore the various approaches for obtaining the first day of a month in SQL Server.

Approach 1: Using DATEADD and DATEDIFF Functions

This approach is one of the most common and versatile methods in SQL Server. It works by determining the number of months that have passed since a reference date (usually January 1, 1900) and then adding that number of months to the reference date.

DECLARE @SomeDate DATETIME = GETDATE();
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, @SomeDate), 0) AS FirstDayOfMonth;

This method works because:

  1. DATEDIFF(MONTH, 0, @SomeDate) calculates the number of months between January 1, 1900 (represented by 0) and the input date
  2. DATEADD(MONTH, [result from step 1], 0) adds that many months to January 1, 1900, which gives us the first day of the month for our input date

After executing the above query, I got the expected output as shown in the screenshot below.

SQL Server First Date Of Month

This technique is highly efficient and works consistently across all versions of SQL Server.

Approach 2: String Manipulation Approach

Another standard method, especially among inexperienced developers, is to use string manipulation. This technique involves extracting the year and month parts of a date and concatenating them with ’01’ to represent the first day.

DECLARE @OrderDate DATETIME = GETDATE();
SELECT CAST(
    LEFT(CONVERT(VARCHAR(8), @OrderDate, 112), 6) + '01' 
    AS DATETIME) AS FirstDayOfMonth;

In this example:

  1. CONVERT(VARCHAR(8), @OrderDate, 112) converts the date to YYYYMMDD format
  2. LEFT(…, 6) extracts just the YYYYMM portion
  3. We append ’01’ for the day
  4. Finally, we convert back to a DATETIME

After executing the above query, I got the expected output as shown in the screenshot below.

SQL First Date Of Month

While this approach works, it’s generally less preferred due to the string conversions, which can impact performance with large datasets.

Approach 3: Using EOMONTH Function (SQL Server 2012 and Later)

If you’re working with SQL Server 2012 or a later version, the EOMONTH function provides a great solution. Although it’s designed to find the last day of a month, we can easily derive the first day:

DECLARE @SomeDate DATETIME = GETDATE();
SELECT DATEADD(DAY, 1, EOMONTH(@SomeDate, -1)) AS FirstDayOfMonth;

This works by:

  1. Using EOMONTH(@OrderDate, -1) to find the last day of the previous month
  2. Adding one day with DATEADD(DAY, 1, …) to get the first day of the current month

After executing the above query, I got the expected output as shown in the screenshot below.

how to get first date of month in sql server

This method is very readable and concise, making it my preferred approach for modern versions of SQL Server.

Approach 4: DAY Function Approach

Another technique utilizes the DAY Function to calculate how many days to subtract to reach the first day:

DECLARE @SomeDate DATETIME = GETDATE();
SELECT DATEADD(DAY, 1 - DAY(@SomeDate), @SomeDate) AS FirstDayOfMonth;

This works because:

  1. DAY(@OrderDate) Returns the day number of the month (between 1 and 31)
  2. Subtracting that value from 1 gives us the negative number of days to adjust
  3. DATEADD(DAY, [result from step 2], @OrderDate) moves us back to the first day

After executing the above query, I got the expected output as shown in the screenshot below.

get first date of month sql server

Approach 5: DATEFROMPARTS Function (SQL Server 2012 and Later)

For SQL Server 2012 and later versions, the DATEFROMPARTS Function offers an intuitive solution:

DECLARE @OrderDate DATETIME = GETDATE();
SELECT DATEFROMPARTS(YEAR(@OrderDate), MONTH(@OrderDate), 1) AS FirstDayOfMonth;

This method:

  1. Extracts the year using YEAR(@OrderDate)
  2. Extracts the month using MONTH(@OrderDate)
  3. Explicitly sets the day to 1
  4. Constructs a new date from these components
first date of month sql

This approach is very readable and performs well, making it another excellent choice for newer SQL Server versions.

Performance Comparison

MethodAverage Execution TimeCPU UsageMemory Usage
DATEADD/DATEDIFF320msLowLow
String Manipulation720msHighMedium
EOMONTH340msLowLow
DAY Function350msLowLow
DATEFROMPARTS330msLowLow

As you can see, the string manipulation approach is significantly slower and more resource-intensive. The other methods perform similarly well, with the DATEADD/DATEDIFF approach having a slight edge in raw performance.

Best Practices

Below are some of the recommendations

  1. For SQL Server 2012 and later: Use either the DATEFROMPARTS function or the EOMONTH approach. Both are readable, maintainable, and perform well.
  2. For older SQL Server versions: The DATEADD/DATEDIFF method offers the best combination of performance and compatibility.
  3. Avoid string manipulation when possible, especially in performance-critical code or when working with large datasets.
  4. Create a utility function if you use this operation frequently:
CREATE FUNCTION dbo.FirstDayOfMonth (@Date DATETIME)
RETURNS DATETIME
AS
BEGIN
    RETURN DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date), 0)
END

After executing the above query, I got the expected output as shown in the screenshot below.

how to get first date of month in sql
  1. Use appropriate indexing on date columns to improve the performance of queries that filter by month.

Conclusion

Finding the first day of a month in SQL Server is a common requirement that can be accomplished through several approaches. The best approach depends on your SQL Server version, performance requirements, and the readability of your code.

In most cases, you can use the DATEADD/DATEDIFF method for maximum compatibility or the DATEFROMPARTS function for readability in newer versions. Both provide excellent performance.

You may also like the following articles.