Why this T-SQL batch give wrong output ?

Question

In MS SQL it is error to calculate month in given date.

If I want result to 1 month then it is giving us result for only 28 days in report. please check it.

select DATEADD(MONTH,1,'2022-02-28')

result

2022-03-28 00:00:00.000

(1 row affected)

Completion time: 2022-05-25T17:32:22.0875649+05:30

Answer 1

It seems you are expecting the last day of the month as the result of the DATEADD function. This will be the case only when result month has the same or fewer days than the specified date. That is not the case here because March has more days than February.

Add EOMONTH to get the last day of the following month:

SELECT EOMONTH(DATEADD(MONTH,1,'2022-02-28')); -- returns 2022-03-31

Answer 2

Just to add to Dan's response.

"DATEADD(MONTH,1" simply increments the month by 1. It does not change the day, unless the day does not appear in that month.

select DATEADD(MONTH,1,'2022-03-31')

Returns:

2022-04-30 00:00:00.000



select DATEADD(MONTH,-1,'2022-03-31')

Returns:

2022-02-28 00:00:00.000

Answer 3

Hi,@Ranjit Srivastva

Continue to complement the discussion above . According to the explanation of the official documentation , if datepart is a month , and the number of days in the date-month is more than the number of days in the returned month , and the date-of-day does not exist in the returned month , the last day of the returned month is returned.

If you want to get the last day of the previous month, you can do something like this:

DECLARE @date DATETIME = '2022-03-31'  
SELECT  
           CASE WHEN DATEDIFF(MONTH, @date, DATEADD(DAY, 1, @date)) = 1 THEN     
           DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @date), 0)) ELSE DATEADD(MONTH, -1, @date)end  

return result：

select DATEADD(MONTH,1,'2022-02-28')  

The operation here is to find a date within a month from the start date , so ‘2022-02-28’ will find the corresponding March record , but the function will not provide a value for the end of the month.
For more questions about DateADD, please refer to this link.

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.