'SSIS Expression to get last day of the month given YYYYMM

I am trying to figure out how to get the last day of the month given with a SSIS Expression.

I receive the column as an Integer, then added a Data Conversion step to convert the YYYMM to a date format. Then on derived column, my expression is:

REPLACE((DT_WSTR,10)(DT_DBDATE)DATEADD("D",-(DAY(DATEADD("M",1,[Copy of MONTH]))),DATEADD("M",1,[Copy of MONTH])),"-","")

And on final table I am getting the value 24520504.

Example: I am getting the value for the derived column from an .xls file. On that file, I use the column MONTH and it is an Integer (example: 201711). On the final table it is an Integer too, but it has the last day from 201711 (example: 20171131)

EDIT: After your help, I reformulate my expression but I still get an error:

REPLACE((DT_STR,10,1252)DATEADD("d",-1,DATEADD("m",1,(DT_DBDATE)(DT_STR,6,1252)[MONTH])),"-","")
sql-serverssis


Solution 1:[1]

where are you starting (this will return last date of month in date format)?

in SQL:

declare @YYYYMM as int = 201811
select dateadd(d,-1,dateadd(m,1,cast(cast(@YYYYMM as varchar(6))+'01' as date)))

This will take it all the way to int:

declare @YYYYMM as int = 201811
select cast(replace(cast(dateadd(d,-1,dateadd(m,1,cast(cast(@YYYYMM as varchar(6))+'01' as date))) as varchar(10)),'-','') as int)

In SSIS:

it's similar. I don't have a way to confirm it right now though. I'll edit later.

Solution 2:[2]

this should work...

periodo ='201803'

DATEADD("day",-1,DATEADD("month",1,(DT_DBDATE)((DT_STR,4,1252)LEFT(period,4) + "-" + RIGHT(period,2) + "-01")))

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 Suraj Rao

Related Questions