How to do Date Calculation in Design Studio

[Former Member]

Purpose:

Design Studio doesn’t have the capacity to do any calculation with dates in the front end scripting. E.g. To add/subtract a number of days to/from today’s date there is no one line code that you can write. In short, date functions are not available Design Studio in today’s date. The following script code will be useful in a scenario where you need to pass a date and a number of days to be added/subtracted and get the new date back.

Steps used in the below particular scenario:

1. Define a date field in design studio application.


2. Define a function in global script as below:
   
   
   
   1. Define two non-array parameters
      
      
      
      1. “pToDate” Type Datefield – Date is read from the datefield into this parameter
      
      
      2. “pNoOfDays” Type int – This is the Number of days to be added or subtracted. Pass a number with negative sign to subtract.
      
      
      
      
   
   
   2. Copy the code given below to the body of the function in design studio.
   
   
   3. The function will return the date in “YYYYMMDD” format. To change the format just modify the last but 1 line of the code given below.
   
   
   
   

Design Studio Code:

var dt1 = pToDate.getDate();

var yyyy1 = Convert.stringToInt(Convert.subString(dt1, 0, 4));

var mm1 = Convert.stringToInt(Convert.subString(dt1, 4, 6));

var dd1 = Convert.stringToInt(Convert.subString(dt1, 6, 8));



mm1 = mm1+9 - Convert.stringToInt(Convert.subString(Convert.floatToString((mm1+9) / 12.0, "0.000"),0,1))*12;

yyyy1 = yyyy1 - Convert.stringToInt(Convert.subString(Convert.floatToString(mm1/10), 0,  Convert.indexOf(Convert.floatToString(mm1/4), '.')));



var date_number1 = 365*yyyy1 +   Convert.stringToInt(Convert.subString(Convert.floatToString(yyyy1/4), 0, Convert.indexOf(Convert.floatToString(yyyy1/4), '.'))) -

Convert.stringToInt(Convert.subString(Convert.floatToString(yyyy1/100), 0, Convert.indexOf(Convert.floatToString(yyyy1/100), '.'))) +

Convert.stringToInt(Convert.subString(Convert.floatToString(yyyy1/400), 0, Convert.indexOf(Convert.floatToString(yyyy1/400), '.'))) +

Convert.stringToInt(Convert.subString(Convert.floatToString((mm1*306+5) / 10), 0, Convert.indexOf(Convert.floatToString((mm1*306+5) / 10), '.'))) + (dd1-1);



date_number1 = date_number1 + pNoOfDays;



yyyy1 = Convert.stringToInt(Convert.subString(Convert.floatToString((10000*date_number1

+ 14780) / 3652425), 0, Convert.indexOf(Convert.floatToString((10000*date_number1 + 14780) / 3652425), '.')));



var ddd = date_number1 - (365*yyyy1 +

Convert.stringToInt(Convert.subString(Convert.floatToString(yyyy1/4), 0, Convert.indexOf(Convert.floatToString(yyyy1/4), '.'))) -

Convert.stringToInt(Convert.subString(Convert.floatToString(yyyy1/100), 0, Convert.indexOf(Convert.floatToString(yyyy1/100), '.'))) +

Convert.stringToInt(Convert.subString(Convert.floatToString(yyyy1/400), 0, Convert.indexOf(Convert.floatToString(yyyy1/400), '.'))));



    if (ddd<0) {

        yyyy1 = yyyy1 - 1;

        ddd = date_number1 - (365*yyyy1 + Convert.stringToInt(Convert.subString(Convert.floatToString(yyyy1/4), 0, Convert.indexOf(Convert.floatToString(yyyy1/4), '.'))) - Convert.stringToInt(Convert.subString(Convert.floatToString(yyyy1/100), 0, Convert.indexOf(Convert.floatToString(yyyy1/100), '.'))) +

Convert.stringToInt(Convert.subString(Convert.floatToString(yyyy1/400), 0, Convert.indexOf(Convert.floatToString(yyyy1/400), '.'))));

}



var mi = Convert.stringToInt(Convert.subString(Convert.floatToString((100*ddd + 52) /

3060), 0, Convert.indexOf(Convert.floatToString((100*ddd + 52) / 3060), '.')));



mm1 = mi+3 - Convert.stringToInt(Convert.subString(Convert.floatToString((mi+2) / 12.0, "0.000"),0,1))*12;

yyyy1 = yyyy1 + Convert.stringToInt(Convert.subString(Convert.floatToString((mi+2) / 12), 0, Convert.indexOf(Convert.floatToString((mi+2) / 12), '.')));

dd1 = ddd - Convert.stringToInt(Convert.subString(Convert.floatToString((mi*306 + 5)/ 10), 0, Convert.indexOf(Convert.floatToString((mi*306 + 5) / 10), '.'))) + 1;



dt1 = Convert.floatToString(yyyy1,"0000")+Convert.floatToString(mm1,"00")+Convert.floatToString(dd1,"00");

return dt1;

How it works:

This script is based on the following algorithm.

Calculate day number from date.

All division is integer division, operator % is modulus.
Given integer y, m, d, calculate day number g as:

function g(y,m,d)



m = (m + 9) % 12

y = y - m/10

return 365*y + y/4 - y/100 + y/400 + (m*306 + 5)/10 + ( d - 1 )



Calculate date from day number



All division is integer division, operator % is modulus.

Given day number g, calculate year, month, and day:



function d(g)

y = (10000*g + 14780)/3652425

ddd = g - (365*y + y/4 - y/100 + y/400)

if (ddd < 0) then

y = y - 1

ddd = g - (365*y + y/4 - y/100 + y/400)

endif



mi = (100*ddd + 52)/3060

mm = (mi + 2)%12 + 1

y = y + (mi + 2)/12

dd = ddd - (mi*306 + 5)/10 + 1



return y, mm, dd

http://alcor.concordia.ca/~gpkatch/gdate-algorithm.htmlTo understand why (and maybe how J) it works have a look here.

http://alcor.concordia.ca/~gpkatch/gdate-method.html

Optimisation Ideas:

I feel this code can be optimised further by replacing few functions. So feel free to add your input.