Date function - First day of month (EOMONTH function, T-SQL)

It seems like  a very easy solution to get the first day in a month for a specific date and actually it is very simple, but here is a good approach and solution which can save your time using it often. Personally, I've been facing this problem through so many reporting and database development tasks.

TRADIOTIONAL METHOD 

This is what I've seen people doing so often and I call this method traditional because more code is involved here:

DECLARE @date date
SET @date = '2013-08-08'

SELECT MyDate = @date

  /*TRADITIONAL METHOD*/  
SELECT FirstDateInAMonth = CAST(YEAR(@date) AS VARCHAR(4)) + '-' + CAST(MONTH(@date) AS VARCHAR(2)) +'-'+ '01'

--Month part is fixed adding leading zero where this should impact 2 digit months
--One way how to do it would be using RIGHT function
SELECT FirstDateInAMonth = CAST(YEAR(@date) AS VARCHAR(4)) + '-' + RIGHT('0' + CAST(MONTH(@date) AS VARCHAR(2)), 2) + '-' + '01'


--Let's try if this is working with 2 digit months
SET @date = '2013-12-08' --> DECEMBER
SELECT FirstDateInAMonth = CAST(YEAR(@date) AS VARCHAR(4)) + '-' + RIGHT('0' + CAST(MONTH(@date) AS VARCHAR(2)), 2) + '-' + '01'


USING EOMONTH SQL FUNCTION

This is very easy to get using EOMONTH function, but sometimes result can depend on SET LANGUAGE option.

DECLARE @date date
SET @date = '2013-08-08'

SELECT MyDate = @date

--End of month date
 SELECT EOMONTH(@date)

--Last day of previous monthSELECT DATEADD(M, -1, EOMONTH(@date))
SELECT EOMONTH(@date, -1)

--First day in a month
SELECT DATEADD(D,1, EOMONTH(@date, -1))


CREATING THE USER FUNCTION


CREATE FUNCTION [dbo].[fn_GetFirstDateInAMonth]
( 
@date datetime
)
RETURNS date
AS
BEGIN
  
  DECLARE @outputDate date
  SELECT @outputDate = DATEADD(D,1, EOMONTH(@date, -1))


  RETURN @outputDate


END

--Using the function
 SELECT [dbo].[fn_GetFirstDateInAMonth](@date)



Mike C.