'SSIS Expression to get last day of previous month in format yyyy/mm/dd

Trying to get the Last day of previous month I'm able to get the first day of the previous month like this (DT_WSTR, 4)YEAR(DATEADD("mm", -1, GETDATE())) +"/"+ RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("mm", -1, GETDATE())),2)+"/"+"01"

which returns 2022/04/01 but I'm really looking to get 2022/04/30. I'm thinking maybe subtract a day from the first day of the current month? I'm just not sure how to write that.

Thank you all in advance

sqlsql-serverdatessis


Solution 1:[1]

c# script is so much easier.

DateTime FirstDayCurrentMonth = DateTime.ParseExact(
     DateTime.Today.ToString("yyyyMM01"), "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture);
DateTime LastDayPriorMonth = FirstDayCurrentMonth.AddDays(-1);

Now that you have the correct date just cast to string in format you want.

strDate = LastDayPriorMonth.ToString("yyyy/MM/dd")";

This might even be easier:

DateTime.Today.AddDays(-DateTime.Today.Day).ToString("yyyy/MM/dd");

Solution 2:[2]

Here is the SQL;

SELECT CONVERT(VARCHAR(10), DATEADD(day, -1, DATEADD(m, DATEDIFF(m, 0, getdate()), 0)),111)

Solution 3:[3]

In SSIS expressions (if you don't want to reach SQLServer for this), remove the days:

DATEADD( "day",  -DAY( GETDATE() ) ,GETDATE() )

Or as a string holding formatted date:

(DT_WSTR,4) YEAR(DATEADD( "day", -DAY( GETDATE() ) ,GETDATE())) + "/"
+RIGHT("0"+(DT_WSTR,2) MONTH(DATEADD( "day",  -DAY( GETDATE()) ,GETDATE()) ), 2) +"/"
+RIGHT("0"+(DT_WSTR,2) DAY(DATEADD( "day",  -DAY( GETDATE()) ,GETDATE()) ),2)

Or, relying on the default date to char conversion in SSIS (ISO):

REPLACE(LEFT(( DT_WSTR,30) DATEADD( "day", -DAY( GETDATE() ) ,GETDATE() ) ,10), "-","/")

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2
Solution 3

Related Questions