MSSQL GETDATE, return last Year (Number only) from previous month-CodePudding

I need to return the year (number) for the previous month. Like 15-Jan-2023, returns 2022. Simple but I could not find any related solution. So far I´m using -31 days but this is not as good as February I need to take care and adjust.

< year(getdate()-32)

CodePudding user response：

SELECT DATEPART(YEAR,DATEADD(MONTH,-1,GETDATE()))

example:

SELECT DATEPART(YEAR,DATEADD(MONTH,-1,'15-JAN-2023'))

returns 2022

CodePudding user response：

Another way is to use the second argument of EOMONTH() to subtract a month:

SELECT YEAR(EOMONTH(GETDATE(),-1));

However, you're going to have problems if you actually have "dates" like 15-JAN-2023, which can only be converted to a date by SQL Server in certain languages. To be safe, you'd have to explicitly set the language to English first (or use try_parse() with an explicit culture, but the CLR overhead isn't worth it IMHO). So if you're storing dates in string columns I strongly recommend fixing that first.