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

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

CodePudding user response：

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");

CodePudding user response：

Here is the SQL;

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

CodePudding user response：

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), "-","/")