How to get the last business day of the given month.
Moderators: chulett, rschirm, roy
How to get the last business day of the given month.
Hello Experts,
I have an issue which sholud bring the last working date or Business date in a month.So how can i do this with the help of datastage Functions.Since my job having Source as Txt file and Target is DB2.
Ex:-
Date from Source:-
2009-05-02
Target sholud be:-
2009-05-29 (friday)
plz help me yaar......
I have an issue which sholud bring the last working date or Business date in a month.So how can i do this with the help of datastage Functions.Since my job having Source as Txt file and Target is DB2.
Ex:-
Date from Source:-
2009-05-02
Target sholud be:-
2009-05-29 (friday)
plz help me yaar......
divya
What is a "working" date? Do you need to handle holidays as well?
Either leverage an existing Time dimension or convert the date to the first of the next month and then subtract one day from it and see if that is a "working" day. If not, keep going backwards until you hit one.
Either leverage an existing Time dimension or convert the date to the first of the next month and then subtract one day from it and see if that is a "working" day. If not, keep going backwards until you hit one.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Hey i found very easy way to get Last business date
Replace lnkname.colname with your linkName.ColName accordingly :D
If (WeekdayFromDate( (DateFromDaysSince(-1,StringToDate( (If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='12' Then YearFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))+1 Else YearFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))):"-": (If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='11' Then '12' Else If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='10' Then '11' Else If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='9' Then '10' Else If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='12' Then '01' Else 0:MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))+1):"-":'01',"%yyyy-%mm-%dd"))),'Sunday')) ='0' Then Left((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")),7):"-": Right((DateFromDaysSince(-1,StringToDate( (If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='12' Then YearFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))+1 Else YearFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))):"-": (If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='11' Then '12' Else If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='10' Then '11' Else If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='9' Then '10' Else If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='12' Then '01' Else 0:MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))+1):"-":'01',"%yyyy-%mm-%dd"))),2)-2 Else If (WeekdayFromDate( (DateFromDaysSince(-1,StringToDate( (If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='12' Then YearFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))+1 Else YearFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))):"-": (If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='11' Then '12' Else If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='10' Then '11' Else If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='9' Then '10' Else If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='12' Then '01' Else 0:MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))+1):"-":'01',"%yyyy-%mm-%dd"))),'Sunday')) ='6' Then Left((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")),7):"-": Right((DateFromDaysSince(-1,StringToDate( (If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='12' Then YearFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))+1 Else YearFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))):"-": (If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='11' Then '12' Else If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='10' Then '11' Else If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='9' Then '10' Else If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='12' Then '01' Else 0:MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))+1):"-":'01',"%yyyy-%mm-%dd"))),2)-1 Else Left((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")),7):"-": Right((DateFromDaysSince(-1,StringToDate( (If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='12' Then YearFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))+1 Else YearFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))):"-": (If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='11' Then '12' Else If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='10' Then '11' Else If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='9' Then '10' Else If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='12' Then '01' Else 0:MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))+1):"-":'01',"%yyyy-%mm-%dd"))),2)
If (WeekdayFromDate( (DateFromDaysSince(-1,StringToDate( (If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='12' Then YearFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))+1 Else YearFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))):"-": (If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='11' Then '12' Else If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='10' Then '11' Else If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='9' Then '10' Else If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='12' Then '01' Else 0:MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))+1):"-":'01',"%yyyy-%mm-%dd"))),'Sunday')) ='0' Then Left((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")),7):"-": Right((DateFromDaysSince(-1,StringToDate( (If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='12' Then YearFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))+1 Else YearFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))):"-": (If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='11' Then '12' Else If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='10' Then '11' Else If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='9' Then '10' Else If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='12' Then '01' Else 0:MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))+1):"-":'01',"%yyyy-%mm-%dd"))),2)-2 Else If (WeekdayFromDate( (DateFromDaysSince(-1,StringToDate( (If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='12' Then YearFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))+1 Else YearFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))):"-": (If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='11' Then '12' Else If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='10' Then '11' Else If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='9' Then '10' Else If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='12' Then '01' Else 0:MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))+1):"-":'01',"%yyyy-%mm-%dd"))),'Sunday')) ='6' Then Left((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")),7):"-": Right((DateFromDaysSince(-1,StringToDate( (If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='12' Then YearFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))+1 Else YearFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))):"-": (If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='11' Then '12' Else If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='10' Then '11' Else If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='9' Then '10' Else If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='12' Then '01' Else 0:MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))+1):"-":'01',"%yyyy-%mm-%dd"))),2)-1 Else Left((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")),7):"-": Right((DateFromDaysSince(-1,StringToDate( (If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='12' Then YearFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))+1 Else YearFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))):"-": (If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='11' Then '12' Else If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='10' Then '11' Else If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='9' Then '10' Else If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='12' Then '01' Else 0:MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))+1):"-":'01',"%yyyy-%mm-%dd"))),2)
divya
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
That appears quite a huge code - especially to debug in case of any issues.
Didn't go through it.
Time dimension is the best answer.
But assume it can also be resolved as
Need to refine few the above code but I leave that to you.
Didn't go through it.
Time dimension is the best answer.
But assume it can also be resolved as
Code: Select all
svLastDayOfCurrMonth =
(If CurrMM = 12
Then CurrYYYY : '-12-31'
Else (CurrYYYY : '-' : CurrMM + 1 : -01') - 1 )
svWeekDay=WeekDayFromDate(svFirstDayOfNextMonth)
svRequiredDate =
(If svWeekDay in (Sat, Sun)
Then PreviousWeekDay(svLastDayOfCurrMonth, 'Fri')
Else svLastDayOfCurrMonth)
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Indeed, at least one Friday is a holiday in Christian cultures, and Friday is not a business day in Islamic cultures.
A Time (or Calendar) dimension is the preferred solution. A Time dimension at daily granularity might even include a "last business day of month" column if that makes good business sense.
A Time (or Calendar) dimension is the preferred solution. A Time dimension at daily granularity might even include a "last business day of month" column if that makes good business sense.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.