to check if a date falls in between two other dates.

[tekivijay]

Hi

Here is my situation

I have a custom table (expense lines) which has a Date field.   I need to check if the date falls between a specific fiscal period.   The fiscal periods are in the fiscal_period table.   the Fiscal Period would have periods like below

Once I get the fiscal period I need to validate if the said record in Expense lines matches the fiscal period in the cost plan table

Can someone help me with a script on this

Much appreciate

Vijay

[Sabrina10]

The OOB script include is part of Jakarta.   I just found it when I realized we didn't have it either.     I'm going to install it on Istanbul and so some testing to see how it functions.     Has anyone else had experience pulling this script include down from a higher version?

[tomoldovan1]

In our case, we were passing date field so had to convert to date/time to do comparison.   Originally using fiscal_start/end_date_time worked but stores time in GMT, so we got unexpected results at start and end of fiscal period.   To work around, used start/end_date_time in the fiscal_period table as it stores as 20170819T130000.   Converted passed in time to string for comparison.   Works well.

Here is the script include:

var FiscalPeriodHelper = Class.create();

FiscalPeriodHelper.prototype = Object.extendsObject(AbstractAjaxProcessor, {

      getFiscalPeriod: function () {

      //*** Variables *** //

            var cdat = this.getParameter('sysparm_date');                    

              var dat = new GlideDateTime(cdat);

                var datstr = dat.toString();

                  var year = datstr.substring(0,4);

                    var month = datstr.substring(5,7);

                      var day = datstr.substring(8,10);

                        //*** Build String to compare against fiscal dates***//

                        var dat2comp = year+month+day+"T"+"000000";

      //**** Query fiscal_period table to find correct period ****//

            var rec = new GlideRecord ('fiscal_period');

            rec.addQuery("start_date_time","<=", dat2comp );          

            rec.addQuery("end_date_time", ">=", dat2comp);

            rec.addQuery('fiscal_type', '445period')

            rec.addQuery('open',true);

            rec.query();

                    while (rec.next())

                      {

                        ans = rec.sys_id;      

                      }

      //*** Return answer to caller ***//

                        return ans;

}

});

[maymunTesm]

Hi, has this been solved yet? Looking for the answer to this too! thanks