How to get the First day and last day of the month from given month number and year in SQL Server-CodePudding

I have a month number and year: month 2 and year 2022.

How can I get the first day of that month like 2022-02-01 and last day of month 2022-02-28?

I have seen many posts on getting first and last date of month based on given date or the current date, but I need it based on given month and year.

Thanks in advance

Here is my stored procedure:

ALTER PROCEDURE [dbo].[Rpt_ItemsSales_DayMonthYear_year]-- 2022
    @year int = NULL
AS
    ;WITH months(MonthNumber) AS
    (
        SELECT 1
        UNION ALL
        SELECT MonthNumber   1
        FROM months
        WHERE MonthNumber < 12
    )
    SELECT
        SalesPos_Dtls.ItemName,
        SUM(SalesPos_Dtls.Qty) AS SumQty,
        SUM(SalesPos_Dtls.TotalPrice) AS SumTotal,
        SalesPos_Dtls.ItemCode,
        DATENAME(month, DATEADD(month, m.MonthNumber, 0) - 1) AS MonthName,
        m.MonthNumber
    FROM
        months AS m
    LEFT JOIN
        SalesPos ON MONTH(SalesPos.StartDate) = m.MonthNumber 
                 AND (status = 'IsPosted') 
                 AND (@year = YEAR(salespos.startdate) OR @year IS NULL)
    LEFT JOIN 
        [dbo].SalesPos_Dtls ON SalesPos.ID = SalesPos_Dtls.OrderId 
    GROUP BY
        m.MonthNumber, dbo.SalesPos_Dtls.ItemName, dbo.SalesPos_Dtls.ItemCode

And this is as far as I got

ALTER PROCEDURE [dbo].[Rpt_ItemsSales_DayMonthYear_year] --2022,1
    @year int = NULL,
    @month int = NULL
AS
    DECLARE @yearr int = @year
    DECLARE @monthh int = @month

    ;WITH months(MonthNumber) AS
    (
        SELECT 1
        UNION ALL
        SELECT MonthNumber   1
        FROM months
        WHERE MonthNumber < 12
    )
    SELECT 
        SalesPos_Dtls.ItemName,
        SUM(SalesPos_Dtls.Qty) AS SumQty,
        SUM(SalesPos_Dtls.TotalPrice) AS SumTotal,
        SalesPos_Dtls.ItemCode,
        DATENAME(month, DATEADD(month, m.MonthNumber, 0) - 1) AS MonthName,
        m.MonthNumber,
        DATEFROMPARTS (@yearr, @monthh, 1) AS MonthStart,
        EOMONTH (DATEFROMPARTS (@yearr, @monthh, 1)) AS MonthEnd
    FROM
        months AS m
    LEFT JOIN
        SalesPos ON MONTH(SalesPos.StartDate) = m.MonthNumber 
                 AND (status = 'IsPosted') 
                 AND (@year = YEAR(salespos.startdate) OR @year IS  NULL)
    LEFT JOIN 
        [dbo].SalesPos_Dtls ON SalesPos.ID = SalesPos_Dtls.OrderId 
    WHERE
        (MONTH(SalesPos.StartDate) = @month OR @month IS NULL)
    GROUP BY
        m.MonthNumber, dbo.SalesPos_Dtls.ItemName, dbo.SalesPos_Dtls.ItemCode

Screenshot with the sample data:

data

But I want my data this way :

هوت دوج  لارج   3.0000  75.0000 

76  January 1   2022-1-01   2022-1-31

هوت دوج ميديم   1.0000  20.0000 77  January 1   2022-1-01   2022-1-31

NULL    NULL    NULL    NULL    February    2   2022-2-01   2022-2-28

NULL    NULL    NULL    NULL    March   3   NULL    2022-3-01    2022-3-31

CodePudding user response：

SET DATEFIRST 1

DECLARE @Month  smallint= 3, @Year   smallint = 2022;

/*First and Last day of month as DATE */
select   DATEFROMPARTS(@Year, @MONTH, 1)FirstDayOfMonth , EOMONTH(DATEFROMPARTS(@Year, @MONTH, 1)) LastDayofMonth

/*First and Last Weekday of Month */
select  DATEPART( dw, DATEFROMPARTS(@Year, @MONTH, 1) ) FirstWeekDayOfMonth , DATEPART(dw, (EOMONTH(DATEFROMPARTS(@Year, @MONTH, 1)))) LastWeekDayofMonth

CodePudding user response：

i dont know if this is the right way to update the question but here is the solution that worked for me.

SELECT DATEADD(month, DATEDIFF(month, 0, @mydate), 0) AS StartOfMonth
SELECT EOMONTH(@mydate) as LastOFMonth

And thanks edit: this is the stored procedure. all i wanted is to have 2 columns infront of each item that have the firstday of the month and the last day witch this item was purchased. i hope this and the stored procedure explain what i mean.

ALTER proc [dbo].[Rpt_ItemsSales_DayMonthYear_year]--Rpt_ItemsSales_DayMonthYear_year 2022
@year int=null
as
;WITH months(MonthNumber) AS
(
    SELECT 1
    UNION ALL
    SELECT MonthNumber 1
    FROM months
    WHERE MonthNumber < 12
)
select SalesPos_Dtls.ItemName,sum(SalesPos_Dtls.Qty) as SumQty,sum(SalesPos_Dtls.TotalPrice) as SumTotal,SalesPos_Dtls.ItemCode,DateName( month , DateAdd( month , m.MonthNumber , 0 ) - 1 ) as MonthName,
m.MonthNumber, cast(DATEADD(month, DATEDIFF(month, 0, SalesPos.StartDate), 0) as date) AS StartOfMonth,EOMONTH(SalesPos.StartDate) as LastOFMonth
from months as m
left join SalesPos on month(SalesPos.StartDate) = m.MonthNumber and (status = 'IsPosted') and (@year = year(salespos.startdate) or @year is null)
left JOIN [dbo].SalesPos_Dtls  on SalesPos.ID=SalesPos_Dtls.OrderId 
group by m.MonthNumber,dbo.SalesPos_Dtls.ItemName,dbo.SalesPos_Dtls.ItemCode,cast(DATEADD(month, DATEDIFF(month, 0, SalesPos.StartDate), 0) as date),EOMONTH(SalesPos.StartDate)