Page 1 of 1
Converting months in to date
Posted: Mon Aug 06, 2007 8:26 am
by siva4joy
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.
Posted: Mon Aug 06, 2007 2:39 pm
by ray.wurlod
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.
Posted: Mon Aug 06, 2007 4:22 pm
by ArndW
siva4joy - could you give some samples of the data, perhaps a programmed function might not be necessary
Posted: Tue Aug 07, 2007 5:23 am
by siva4joy
the values are like this
2002-07-07,2005-05-05(date) and 390,231(char)which is no of months.
my requirement is like this 2002-07-07-(390) and the output is again a date.
Posted: Tue Aug 07, 2007 4:12 pm
by ArndW
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.
Posted: Wed Aug 08, 2007 4:17 am
by siva4joy
is there any function or code which will give (no of months/12) to a yyyy-mm-dd.
is there any logic how to convert or change.
Posted: Wed Aug 08, 2007 5:10 am
by JoshGeorge
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.
Posted: Thu Aug 09, 2007 8:22 am
by siva4joy
i think it will work
thanks for your help
Posted: Thu Aug 09, 2007 4:14 pm
by ray.wurlod
Be careful if the number of months to be added is negative.
Be careful also to check that the resulting date is valid (for example 31 Jan + 1 month).
Posted: Fri Aug 10, 2007 2:50 am
by JoshGeorge
This will help you to achive what you are looking for (Subtract month/s from date)
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
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.
Posted: Fri Aug 10, 2007 3:24 am
by ray.wurlod
Again, what is the behaviour of Floor() with a negative argument?
Posted: Fri Aug 10, 2007 8:59 pm
by JoshGeorge
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 ...
Posted: Wed Aug 15, 2007 11:01 am
by siva4joy
i am getting the correct values.
thanks for everybody's help.