need help in ssis expression

Question

HI Guys

Need help in the ssis expression. Not sure how to work on it but i do know where it is wrong

Apologies for not defining what @dateext in my ssis expression. So I am editing my original request
Let me start from the scratch

ssis expression for date
RIGHT("0" + (DT_WSTR, 2) DATEPART("DD", DATEADD("day", -1, GETDATE())),2)+" "+(DT_STR,50,1252)TRIM(SUBSTRING("January Febuary March April May June July August September October November December ",((DATEPART("MONTH",GETDATE())-1)*10)-1,10))+" " +RIGHT("0" + (DT_STR,4,1252)DATEPART( "yyyy" , getdate() ), 4)

ssis expression for dateext:-
SUBSTRING( @[User::Date2] ,1, 2 )=="01"?"st":SUBSTRING( @[User::Date2] ,1, 2 )=="21"?"st":SUBSTRING( @[User::Date2] , 1,2 )=="02"?"nd":SUBSTRING( @[User::Date2] , 1,2 )=="22"?"nd":SUBSTRING( @[User::Date2] ,1, 2 )=="03"?"rd":SUBSTRING( @[User::Date2] ,1, 2 )=="23"?"rd":"th"

i have combined both expression and made date
RIGHT("0" + (DT_WSTR, 2) DATEPART("DD", DATEADD("day", -1, GETDATE())),2)+ @[User::DateExt] +" "+(DT_STR,50,1252)TRIM(SUBSTRING("January Febuary March April May June July August September October November December ",((DATEPART("MONTH",GETDATE())-1)*10)-1,10))+" " +RIGHT("0" + (DT_STR,4,1252)DATEPART( "yyyy" , getdate() ), 4)

Basically what i am trying to do is get dates in this format like for example 01st december 2021,22nd december 2021,23rd december ,25th december 2021,and i think i know what the problem is.

My SSIS expression doesnt work on the 1st day of the month. For example if the date is 01st december 2021, what i should be getting from the expression which is 30th November 2021 and i am getting 30th December 2021. When I ran my expression today and since today is 02nd december 2021 i got 01st december 2021 which is correct. So it is the first of each month where my ssis expression is not working

Any help will be much appreciated
Regards
Farhan Jamil

Answer 1

Hi @Farhan Jamil ,

Do you mean you want to get previous date and the format is like 02nd December 2021 and today is 12/3/2021？If I misunderstand your need, please incorrect me.

Check below to see if it could meet your requirement.

Create three variables.

Name: Expression

1. Ydate: dateadd("day", -1, getdate())
2. Date2 RIGHT("0" + (DT_WSTR,2)DAY( @[User::Ydate] ), 2)
3. DateExt @[User::Date2]=="01"?"st":@[User::Date2]=="21"?"st":@[User::Date2]=="02"?"nd":@[User::Date2]=="22"?"nd":@[User::Date2]=="03"?"rd":@[User::Date2]=="23"?"rd":"th"

Combine them with below code:

@[User::Date2] +@[User::DateExt]+" "+(MONTH(@[User::Ydate]) == 1 ? "Jan" : MONTH(@[User::Ydate]) == 2 ? "Feb" : MONTH(@[User::Ydate]) == 3 ? "Mar" :   
  MONTH(@[User::Ydate]) == 4 ? "Apr" : MONTH(@[User::Ydate]) == 5 ? "May" : MONTH(@[User::Ydate]) == 6 ? "Jun" :   
  MONTH(@[User::Ydate]) == 7 ? "Jul" : MONTH(@[User::Ydate]) == 8 ? "Aug" : MONTH(@[User::Ydate]) == 9 ? "Sep" :   
  MONTH(@[User::Ydate]) == 10 ? "Oct" : MONTH(@[User::Ydate]) == 11 ? "Nov" : MONTH(@[User::Ydate]) == 12? "Dec":"")+" " +RIGHT("0" + (DT_STR,4,1252)DATEPART( "yyyy" ,  @[User::Ydate] ), 4)  

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.