How to find the firstday and last day of 3 months back in ssis expression

SSIS and Import/Export
1

How to find the firstday and last day of 3 months back along with year in SSIS expression?

Example: when I run my SSIS package on 10/1 I need file name in this format A_07012015_07312015.xls
(this should be 3 months back first day and last day)

Please can any one help me on this?

Thanks in advance.

2

=Today.AddDays(-(Today.Day - 1 )).AddMonths(-1)
=Today.AddDays(0 - DateTime.Today.Day)

Adjust as needed...

3

I need SSIS expression

I am using this below expression

"A_" + RIGHT( "0" + (DT_STR, 2, 1252) MONTH(DATEADD("MM", -3, GETDATE())), 2)+ "01_"

  • RIGHT( "0" + (DT_STR, 2, 1252) MONTH(DATEADD("MM", -3, GETDATE())), 2)
  • (DT_STR, 2, 1252) DAY(DATEADD( "DD", DAY(GETDATE()) * -1, GETDATE() ))
  • ".xls"

I am getting results as like this with above expression A_0601_0631.xls

But I need year also in filename like A_06012015_06312015.xls

can I have expression to get year.. If I ran the expression on jan 2016 I need to get the file name as A_10012015_10312015.xls ...

Can I have the expression to get 3 months back year please?

4

Why is January "10"

5

i am pulling 3 months back data.. so when i run SSIS package in Jan (i.e 3 months back data means Oct so file name should be Oct first day and last day)

Now i am almost there but i need year expression.. can any one help me on this?

6

so why not YEAR(...)

7

If i add year like that YEAR(DATEADD("YY", -3, GETDATE()) .. when i change the date to Jan and testing i am getting 2013 year instead of 2015..

8

Your expression has "yy", -3, which means subtract three years. Why not "MM", -3 (subtract three months)?

9

Ok got you.. Thanks..

How can i get the last day of 3 months back month?

The below expression giving last day of previuos month.. But i need June month last day..

(DT_STR, 2, 1252) DAY(DATEADD( "DD", DAY(GETDATE()) * -1, GETDATE() ))

10

Please try this

(DT_WSTR,20)Dateadd("DD",-1,dateadd("MM",datediff("MM", (DT_DATE) "1900-01-01",getdate())-2, (DT_DATE) "1900-01-01"))
11

Hi Viggneshwar,

Your expression giving result as 2015-06-30 00:00:00.

But i need the expression as 06302015 , I don't need "-" and time .

Please help me on this..

Thank you..

12

You can use REPLACE to eliminate the "-" and SUBSTRING to chop off the time.

13

This gives exact output which you requires

RIGHT("0"+(DT_WSTR,20)DATEPART("MM",Dateadd("DD",-1,dateadd("MM",datediff("MM", (DT_DATE) "1900-01-01",getdate())-2, (DT_DATE) "1900-01-01"))),2)
+
RIGHT("0"+(DT_WSTR,20)DATEPART("DD",Dateadd("DD",-1,dateadd("MM",datediff("MM", (DT_DATE) "1900-01-01",getdate())-2, (DT_DATE) "1900-01-01"))),2)
+
(DT_WSTR,20)DATEPART("YYYY",Dateadd("DD",-1,dateadd("MM",datediff("MM", (DT_DATE) "1900-01-01",getdate())-2, (DT_DATE) "1900-01-01")))