EOMONTH - Last day of month in SQL Server

In SQL Server 2012, Microsoft has introduced a new in-built function EOMONTH to find out the last date of the month.

In prior version, we have to write our own logical script to find out the same like below

DECLARE @dt DATE
SET @dt = GETDATE()
SELECT CAST(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dt)+1,0)) AS DATE)

EOMONTH returns the last day of the month for the specified date

Syntax

EOMONTH ( start_date [, month_to_add ] )

Input Parameters

• start_date: date for which to return the last day of the month
• month_to_add: Optional integer specifying the number of months to add to start_date. If this parameter is specified, then specified number of months are added to start_date, and then returns the last day of the month for the resulting date.

NOTE: If this addition results in overflow of valid date, then an error is raised.

Let's use EOMONTH function with some examples

Example 1: With first parameter (start_date) only

DECLARE @date DATE = '01/01/2012'
SELECT EOMONTH ( @date ) AS Result

OUTPUT

Example 2: With both parameters (start_date and month_to_add)

DECLARE @date DATE = '01/01/2012'
SELECT EOMONTH (@date) AS 'This Month'
       , EOMONTH (@date, 1) AS 'Next Month'
       , EOMONTH (@date, -1) AS 'Last Month'

OUTPUT

Generate Week Dates for a Year in SQL Server

In this post, we will see how to generate week start and end dates for a given year in SQL Server.

Oftentimes one need to generate Start & End dates for all the weeks in a given year to generate week-wise report starting from Monday to Sunday

Below is the Table-Valued function where you have to pass year and it would return a table with all the weeks and its Start & End Dates for the given year.

CREATE FUNCTION dbo.udf_GetWeekDatesForYear(
    @Year  SMALLINT  
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN   
WITH Dates AS
(
    SELECT    DATEADD(mm,(@Year-1900)*12,0) AS YearStartdate
          , DATEADD(mm,(@Year+1-1900)*12,0)-1 AS YearEnddate
)
, Weeks AS
(
    SELECT    1 as weekno, YearStartdate as StartDate
            , CASE WHEN DATEPART(WEEKDAY, YearStartdate)= 1 THEN YearStartdate ELSE DATEADD(d, (8 - datepart(WEEKDAY, YearStartdate)), YearStartdate) END AS EndDate
    FROM    Dates
    UNION ALL
    SELECT    weekno + 1, DATEADD(d, 1, EndDate)
            , CASE WHEN DATEADD(d, 7, EndDate)<=(select YearEnddate from Dates) THEN DATEADD(d, 7, EndDate) ELSE (select YearEnddate from Dates) END
    FROM    Weeks
    where    EndDate < (select YearEnddate from Dates)
)
SELECT    'Week' + cast(weekno as varchar) as Week, StartDate, EndDate
FROM    Weeks

Lets' execute the script and check the output

SELECT * FROM dbo.udf_GetWeekDatesForYear(2015)

OUTPUT

Refer related post: Generating Week Dates for a month in SQL Server

Generate Week Dates for a Month in SQL Server

In this post, we will see how to generate Week Start and End dates for a given Month in SQL Server.

Oftentimes one need to generate Start & End dates for all the weeks in a given month to generate week-wise report starting from Monday to Sunday to generate a week wise report

Below is the Table-Valued function where you have to pass year and month and it would return a table with all the weeks and its Start & End Dates for the given month.

CREATE FUNCTION dbo.udf_GetWeekDatesForMonth(
      @Year  SMALLINT
    , @Month TINYINT
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN   
WITH Dates AS
(
    SELECT  DATEADD(mm,(@Year-1900)*12+@Month-1,0) AS MonthStartDate
          , DATEADD(mm,(@Year-1900)*12+@Month,0)-1 AS MonthEndDate
)
, Weeks AS
(
    SELECT  1 as weekno, MonthStartDate as StartDate
          , CASE WHEN DATEPART(WEEKDAY, MonthStartDate)= 1 THEN MonthStartDate ELSE DATEADD(d, (8 - datepart(WEEKDAY, MonthStartDate)), MonthStartDate) END AS EndDate
    FROM    Dates
    UNION ALL
    SELECT  weekno + 1, DATEADD(d, 1, EndDate)
          , CASE WHEN DATEADD(d, 7, EndDate)<=(select MonthEndDate from Dates) THEN DATEADD(d, 7, EndDate) ELSE (select MonthEndDate from Dates) END
    FROM    Weeks
    where   EndDate < (select MonthEndDate from Dates)
)
SELECT  'Week' + cast(weekno as varchar) as Week, StartDate, EndDate
FROM    Weeks

Let's execute a script to check the output of the function.

SELECT * FROM dbo.udf_GetWeekDatesForMonth(2015,10)

OUTPUT

Refer related post: Generating Week Dates for a year in SQL Server