question

RanjitSrivastva-8483 avatar image
0 Votes"
RanjitSrivastva-8483 asked BertZhoumsft-7490 edited

Why this T-SQL batch give wrong output ?

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

sql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)

0 Votes 0 ·

Hi,@RanjitSrivastva-8483

Have you tested the script below and please endorse it if it helped you, it will help someone who has a similar problem as you.

Bert Zhou

0 Votes 0 ·
DanGuzman avatar image
2 Votes"
DanGuzman answered

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered TomPhillips-1744 edited

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








5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

BertZhoumsft-7490 avatar image
0 Votes"
BertZhoumsft-7490 answered BertZhoumsft-7490 edited

Hi,@RanjitSrivastva-8483

Welcome to Microsoft T-SQL Q&A Forum!
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:
205588-image.png

 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.




image.png (3.1 KiB)
image.png (1.9 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.