Converting months in to date

siva4joy · Post by **siva4joy** » Mon Aug 06, 2007 8:26 am

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.

ray.wurlod · Post by **ray.wurlod** » Mon Aug 06, 2007 2:39 pm

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.

ArndW · Post by **ArndW** » Mon Aug 06, 2007 4:22 pm

siva4joy - could you give some samples of the data, perhaps a programmed function might not be necessary

siva4joy · Post by **siva4joy** » Tue Aug 07, 2007 5:23 am

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.

ArndW · Post by **ArndW** » Tue Aug 07, 2007 4:12 pm

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.

siva4joy · Post by **siva4joy** » Wed Aug 08, 2007 4:17 am

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.

JoshGeorge · Post by **JoshGeorge** » Wed Aug 08, 2007 5:10 am

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.

siva4joy · Post by **siva4joy** » Thu Aug 09, 2007 8:22 am

i think it will work
thanks for your help

ray.wurlod · Post by **ray.wurlod** » Thu Aug 09, 2007 4:14 pm

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).

JoshGeorge · Post by **JoshGeorge** » Fri Aug 10, 2007 2:50 am

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.

ray.wurlod · Post by **ray.wurlod** » Fri Aug 10, 2007 3:24 am

Again, what is the behaviour of Floor() with a negative argument?

JoshGeorge · Post by **JoshGeorge** » Fri Aug 10, 2007 8:59 pm

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 ...

siva4joy · Post by **siva4joy** » Wed Aug 15, 2007 11:01 am

i am getting the correct values.
thanks for everybody's help.