Add months to a date in Data Management Studio

[AllanD]

Hi All,

We are just starting out using the data management studio 2.6 to validate and cleanse data so if this seems a very basic question I apologise.

We normally receive a start date and end date for a record along with other information in a text file and have used the qkb to validate and cleanse this information prior to printing marketing material for these purchases.

There is one file we receive which has a start date and a duration rather than end date.  The duration is in months and can range from 1 to 24.

We need to calculate the end date from this but I cannot seem to find any way of adding the months to the start date.

Is there any way to do this or is there documentation that could help in identifying a solution?

Any help is greatly appreciated.

THanks,

Allan.

[NicolasRobert · Accepted solution]

Hello,

You can try this.

string start_date_string
integer duration_in_months

date start_date
date end_date
date end_date2
integer start_day
integer start_month
integer start_year

integer end_day
integer end_month
integer end_year

start_date_string='2016-07-14'
duration_in_months=18

/* first solution, some additional checks might be required (end of month) */
start_date=todate(start_date_string)
start_day=tointeger(formatdate(start_date,"DD"))
start_month=tointeger(formatdate(start_date,"MM"))
start_year=tointeger(formatdate(start_date,"YYYY"))

end_day=start_day
if (start_month+duration_in_months)%12==0 then
	end_month=12
else end_month=(start_month+duration_in_months)%12
end_year=start_year+(tointeger((start_month+duration_in_months-1)/12))

end_date=todate(tostring(end_year) & '-' & tostring(end_month) & '-' & tostring(end_day)) /* date validity to be checked if falling end of month */

/* second solution, less accurate */
end_date2=start_date+tointeger(duration_in_months*30.4375)

This page could be of interest.

Hope this helps.

Nicolas.

[AllanD]

Hi Nicolas,

I used the first solution and added the following code to loop through to find the last valid month end date to take care of the end of month issues you raised as an issue.  Not sure if there is a better way but it works.

if isnull(end_date)
end_date=todate(tostring(end_year) & '-' & tostring(end_month) & '-' & tostring(end_day-1))
if isnull(end_date)
end_date=todate(tostring(end_year) & '-' & tostring(end_month) & '-' & tostring(end_day-2))
if isnull(end_date)
end_date=todate(tostring(end_year) & '-' & tostring(end_month) & '-' & tostring(end_day-3))

Thanks for the speedy response.

Allan.