YTD Month Start and End Dates based on the Service Date Column

I need to create Month Start Date and Month End Date columns based on the Service Date column.

If Service Date is current month, then Month End date should be Today's Date.

For example:

If Service date was '2022-02-14', then Month Start and Month End dates associated with the date are '2022-01-01' and '2022-02-28'.

However, if Service Date is '2022-04-07' (Current Month), then Month Start and Month End dates associated with the date should be '2022-04-01' and '2022-04-11' (Today's Date).

Regular Start and End Dates of the Month:

CAST(DATEADD(month, DATEDIFF(month, 0, @ServiceDate), 0) AS DATE) AS [Month Start Date],
CAST(EOMONTH(@ServiceDate) AS DATE) AS [Month End Date],

CodePudding user response：

You already have the first of the month logic figured out. For the end of the month, try a case to return today's date if it's the current month. Otherwise, return the last day in the @ServiceDate month

SELECT CAST(DATEADD(month, DATEDIFF(month, 0, @ServiceDate), 0) AS DATE) AS [Month Start Date]
      , CASE WHEN dateDiff(month, @ServiceDate, getDate()) = 0 THEN CAST(getDate() AS DATE) 
             ELSE CAST(EOMONTH(@ServiceDate) AS DATE) 
        END AS [Month End Date]