Get first day of month

To get the first day of the month in SQL Server, you can use several methods depending on the version of SQL Server you are working with. Below are a few common approaches:

1. Using the DATEFROMPARTS function

The DATEFROMPARTS function allows you to construct a date using the year, month, and day parts.

SELECT DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1) AS FirstDayOfMonth;

YEAR(GETDATE()): Extracts the year from the current date.
MONTH(GETDATE()): Extracts the month from the current date.
1: Represents the first day of the month.

This method is straightforward and readable.

2. Using the EOMONTH function with an offset

The EOMONTH function returns the last day of the month, and you can use it with an offset to get the first day of the month.

SELECT DATEADD(DAY, 1, EOMONTH(GETDATE(), -1)) AS FirstDayOfMonth;

EOMONTH(GETDATE(), -1): Gets the last day of the previous month.
DATEADD(DAY, 1, …): Adds one day to get the first day of the current month.

3. Using CONVERT and string manipulation

If you’re working with an older version of SQL Server that doesn’t support DATEFROMPARTS or EOMONTH, you can use string manipulation to get the first day of the month.

SELECT CONVERT(DATE, CONVERT(VARCHAR(7), GETDATE(), 120) + '-01') AS FirstDayOfMonth;

CONVERT(VARCHAR(7), GETDATE(), 120): Converts the date to a string in the format YYYY-MM.
+ ‘-01’: Appends -01 to the string to represent the first day.
CONVERT(DATE, …): Converts the string back to a date.

4. Using DATEADD and DAY functions

You can also use DATEADD and DAY functions to achieve the same result:

SELECT DATEADD(DAY, 1 - DAY(GETDATE()), GETDATE()) AS FirstDayOfMonth;

DAY(GETDATE()): Gets the day of the current date.
1 – DAY(GETDATE()): Calculates the number of days to subtract from the current date to reach the first day of the month.
DATEADD(DAY, …, GETDATE()): Subtracts the calculated number of days from the current date.

5. CASE Expression

DECLARE @Date DATE = GETDATE();
SELECT CASE 
    WHEN DAY(@Date) = 1 THEN @Date
    ELSE DATEADD(DAY, 1 - DAY(@Date), @Date) 
END AS FirstDayOfMonth;

The CASE expression checks if the current date is already the first day of the month. If not, it uses the same logic as the DATEADD function.

Conclusion

The method you choose depends on your SQL Server version and preference for readability. The DATEFROMPARTS and EOMONTH functions are more intuitive for those using SQL Server 2012 or later, while string manipulation and DATEADD offer compatibility with older versions.