Converting months in to date
Moderators: chulett, rschirm, roy
Converting months in to date
hi,
i am having a requirtement for which i needs to convert months into date.
in my source side hae 2 columns date and varchar which is number of months and in the target i am having a date for which the logic is (date-no of months).
can anybody tell me how to convert the no of months to a date.
i am having a requirtement for which i needs to convert months into date.
in my source side hae 2 columns date and varchar which is number of months and in the target i am having a date for which the logic is (date-no of months).
can anybody tell me how to convert the no of months to a date.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Almost certainly you will need to create a function. You can find the logic here but this is a server (BASIC) function. You will need to "translate" it into C++ code.
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.
siva4joy - could you give some samples of the data, perhaps a programmed function might not be necessary
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
That is complex. If the algorithm would accept "close" values, you could convert the date to a julian one then subtract int(months*30.41666) days from it. Otherwise you will need to write some code.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 612
- Joined: Thu May 03, 2007 4:59 am
- Location: Melbourne
FLOOR(No_Of_Months/12) will give you years to be added/subtracted and
(No_Of_Months MOD 12) will give you months to be added/subtracted
THis should give you the lead to your solution.
(No_Of_Months MOD 12) will give you months to be added/subtracted
THis should give you the lead to your solution.
Joshy George
<a href="http://www.linkedin.com/in/joshygeorge1" ><img src="http://www.linkedin.com/img/webpromo/bt ... _80x15.gif" width="80" height="15" border="0"></a>
<a href="http://www.linkedin.com/in/joshygeorge1" ><img src="http://www.linkedin.com/img/webpromo/bt ... _80x15.gif" width="80" height="15" border="0"></a>
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 612
- Joined: Thu May 03, 2007 4:59 am
- Location: Melbourne
This will help you to achive what you are looking for (Subtract month/s from date)
Tweak the above code if you want to add, won't be that difficult. Anyhow your requirement is only to subtract, this should serve the purpose.
Code: Select all
Stage Variables Derivation
Yrs (int) - - > FLOOR(INPUT.No_Of_Months/12)
Mnths (int) - - > Mod(INPUT.No_Of_Months, 12)
Newyr (int) - - > (If (INPUT.curr_date[6,2] <= mnths) Then
INPUT.curr_date[1,4] - 1 Else INPUT.curr_date[1,4]) - yrs
NewMnth (int) - - > If (INPUT.curr_date[6,2] - mnths) <= 0 Then
12 +( INPUT.curr_date[6,2] - mnths) Else INPUT.curr_date[6,2] - mnths
LeapYrInd (int) - - > if Mod(Newyr, 400) = 0 then 4 else
if Mod(Newyr, 100) = 0 then 9 else if Mod(Newyr, 4) = 0 then 4 else 9
NewDay (Varchar) - - > If INPUT.curr_date[9,2] > 28 Then
(if (NewMnth = 2) and (LeapYrInd = 9) Then 28 Else
if ((NewMnth = 4 or NewMnth = 6 or NewMnth = 9 or NewMnth = 11)
and INPUT.curr_date[9,2] > 30) Then 30 Else INPUT.curr_date[9,2])
Else INPUT.curr_date[9,2]
NewDate - - > Newyr:'-': (If LEN(NewMnth) = 1
Then 0:NewMnth Else NewMnth) :'-': NewDay
Joshy George
<a href="http://www.linkedin.com/in/joshygeorge1" ><img src="http://www.linkedin.com/img/webpromo/bt ... _80x15.gif" width="80" height="15" border="0"></a>
<a href="http://www.linkedin.com/in/joshygeorge1" ><img src="http://www.linkedin.com/img/webpromo/bt ... _80x15.gif" width="80" height="15" border="0"></a>
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 612
- Joined: Thu May 03, 2007 4:59 am
- Location: Melbourne
Making sure that 'Number of Months' column is always a positive number should take care of that situation. Comments for the code clearly says this is only for what OP asked. ie. subtract number of months from a given date. As you noted earlier OP should be careful if number of months column carry negative value, given date is valid ...
Joshy George
<a href="http://www.linkedin.com/in/joshygeorge1" ><img src="http://www.linkedin.com/img/webpromo/bt ... _80x15.gif" width="80" height="15" border="0"></a>
<a href="http://www.linkedin.com/in/joshygeorge1" ><img src="http://www.linkedin.com/img/webpromo/bt ... _80x15.gif" width="80" height="15" border="0"></a>