Start of month from date in MS SQL (opposite to EOMONTH function)

Sometimes we need to select values grouped by month from MS SQL Table. Probably the easiest way to do it is to call function DateAdd with DateDiff:

SELECT Sum(YourColumn), DATEADD(month, DATEDIFF(month, 0, [YourDateColumn]), 0) as 'Date'
FROM YourTable
GROUP BY DATEADD(month, DATEDIFF(month, 0, [YourDateColumn]), 0); 

Or we can use EOMONTH function that give us date that is the last day of the month:

SELECT Sum(YourColumn),  EOMONTH([YourDateColumn]) as 'Date'
FROM YourTable
GROUP BY EOMONTH([YourDateColumn]) ; 

But in usual it’s just more convenient to use first day of month. So, if you want to save your time and make you script more clear, you can create you can create your own function, just like EOMONTH:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[SOMONTH] (@dd datetime)
   RETURNS date

AS
BEGIN
	declare @YYYY_MM_DD datetime;
	
	SET @YYYY_MM_DD = DATEADD(month, DATEDIFF(month, 0, @dd), 0);   
	
	return @YYYY_MM_DD
END	

Now we can call it:

SELECT Sum(YourColumn),  SOMONTH([YourDateColumn]) as 'Date'
FROM YourTable
GROUP BY SOMONTH([YourDateColumn]) ; 

Share your thoughts