I have a date in format dd/mm/yyyy. I want to subtract one month from it.

I am using this code but the output is "09/10/2020" I don't know why my code does the subtraction of the year -2 also.

This is my request

SELECT
    FORMAT(CONVERT (DATE, DATEADD(MONTH, -1, CONVERT(char(9), GETDATE()))), 'dd/MM/yyyy')

CodePudding user response：

you need to change it to:

select format(CONVERT (date,DATEADD(MONTH, -1,GETDATE())), 'dd/MM/yyyy' )

but as Larnu stated. it seems like you need to change the column.

CodePudding user response：

Your current code doesn't work as expected because:

SELECT CONVERT(char(9), GETDATE());

Returns this (at least in my language):

Nov  9 20

Which is, unfortunately, and again in my language, a valid date (but in {20}20, not {20}22).

Even in the right style (103), char(9) would yield 10/11/202 tomorrow, since 9 digits is only enough if either the day or month is a single digit.

Don't know why you are converting GETDATE() to a string. Just perform date math on it and then format it if you need to (using a specific style number, e.g. 103 for d/m/y):

SELECT CONVERT(char(10), DATEADD(MONTH, -1, GETDATE()), 103);

I really wouldn't use FORMAT() for such simple output, as the CLR overhead really isn't worth it. Ideally you leave it as a date/time type until presentation time - surely your presentation layer can present your date as d/m/y if that's really a wise idea.

And if you are storing or passing dates as strings (and worse, in regional formats like d/m/y) you really should consider fixing that.