Transformer Date Derivations in DataStage
February 25, 2018
I’ve formulated a few derivations to calculate certain dates (Listed in BOLD). If your time dimension cannot help or you don’t have a time dimension, the following might help. Feel free to add or optimize my derivations. This thread can be a handy reference for many.
Last Day of 2nd Last Month
| Code: |
| DateOffsetByDays(YearFromDate(DateFromDaysSince(-1,Left(BUSINESS_DATE,7):”-01″)):”-“:Right(“0″:MonthFromDate(DateFromDaysSince(-1,Left(BUSINESS_DATE,7):”-01″)),2):”-01″,-1) |
Last Day Of Last Month
| Code: |
| DateOffsetByComponents(YearFromDate(BUSINESS_DATE):”-“:Right(“0″:MonthFromDate(BUSINESS_DATE),2):”-01″,0,0,-1) |
Last Day Of 2nd Last Quarter
| Code: |
| DateOffsetByDays(YearForQtr:”-“: Right(“0″:If ((AsInteger(((MonthFromDate(BUSINESS_DATE)+2)/3)-2)*3)+1)<0 Then ((AsInteger(((MonthFromDate(BUSINESS_DATE)+2)/3)-2)*3)+1)+ 12 Else ((AsInteger(((MonthFromDate(BUSINESS_DATE)+2)/3)-2)*3)+1),2) :”-01″,-1) |
where
| Code: |
| YearforQtr=If AsInteger((MonthFromDate(BUSINESS_DATE)+2)/3)=1 Then YearFromDate(BUSINESS_DATE)-1 Else YearFromDate(BUSINESS_DATE) |
Last Day Of Last Quarter
| Code: |
| DateOffsetByDays(YearFromDate(BUSINESS_DATE):”-“: Right(“0″:If ((AsInteger(((MonthFromDate(BUSINESS_DATE)+2)/3)-1)*3)+1)<0 Then ((AsInteger(((MonthFromDate(BUSINESS_DATE)+2)/3)-1)*3)+1)+ 12 Else ((AsInteger(((MonthFromDate(BUSINESS_DATE)+2)/3)-1)*3)+1),2) :”-01″,-1) |
Last Day Before 12 Months
| Code: |
| DateOffsetByDays(YearFromDate(DateOffsetByComponents(BUSINESS_DATE,0,-12,0)):”-“:Right(“0″:MonthFromDate(DateOffsetByComponents(BUSINESS_DATE,0,-12,0)),2):”-01″,-1) |
Last Day Of Last Financial Year
| Code: |
| If (MonthFromDate(BUSINESS_DATE)>=4 And MonthFromDate(BUSINESS_DATE)<=12) Then (DateOffsetByDays(StringToDate(YearFromDate(BUSINESS_DATE):”-04-01″),-1)) Else (DateOffsetByComponents(StringToDate(YearFromDate(BUSINESS_DATE):”-04-01″),-1,0,-1)) |
Posted in Technology