This function finds if a timestamp falls within the same month, bi-month, quarter, tertial, or half-year as a base date.It is also possible to find if the timestamp falls within a previous or following time period.
The number of months that defines the period. An integer or expression that resolves to an integer that must be one of: 1 (equivalent to the inmonth() function), 2 (bi-month), 3 (equivalent to the inquarter() function), 4 (tertial), or 6 (half year).
timestamp
The date that you want to compare with base_date.
base_date
Date that is used to evaluate the period.
period_no
The period can be offset by period_no, an integer, or expression resolving to an integer, where the value 0 indicates the period that contains base_date. Negative values in period_no indicate preceding periods and
positive values indicate succeeding periods.
first_month_of_year
If you
want to work with (fiscal) years not starting in January, indicate
a value between 2 and 12 in first_month_of_year.
These examples use the date format DD/MM/YYYY. The date format is specified in the SET DateFormat statement at the top of your data load script. Change the format in the examples to suit your requirements.
Scripting examples
Example
Result
inmonths(4, '25/01/2013', '25/04/2013',
0)
Returns True. Because the value of timestamp, 25/01/2013, lies within the four-month period 01/01/2013 to 30/04/2013, in which the value of base_date, 25/04/2013 lies.
inmonths(4, '25/05/2013', '25/04/2013',
0)
Returns False. Because 25/05/2013 is outside the same period as the previous example.
inmonths(4, '25/11/2012', '01/02/2013',
-1 )
Returns True. Because the value of period_no, -1, shifts the search period back one period of four months (the value of n-months), which makes the search period 01/09/2012 to 31/12/2012.
inmonths( 4, '25/05/2006', '01/03/2006',
0, 3)
Returns True. Because the value of first_month_of_year is set to 3, which makes the search period 01/03/2006 to 30/07/2006 instead of 01/01/2006 to 30/04/2006.
Example:
Add the example script to your app and run it. Then add the fields listed in the results column to a sheet in your app to see the result.
This example checks if the invoice date in the table falls in the bi-month period that includes the base_date shifted forwards by one bi-month period (by specifying period_no as 1).
TempTable:
LOAD RecNo() as InvID, * Inline [
InvDate
28/03/2012
10/12/2012
5/2/2013
31/3/2013
19/5/2013
15/9/2013
11/12/2013
2/3/2014
14/5/2014
13/6/2014
7/7/2014
4/8/2014
];
InvoiceData:
LOAD *,
InMonths(2, InvDate, '11/02/2013', 1) AS InMthsPlus1
Resident TempTable;
Drop table TempTable;
The resulting table contains the original dates and a column with the return value of the InMonths() function.
The search period is 01/03/2013 to 30/04/2013, because the value of base_date is shifted forwards two months from the value in the function (11/02/2013).
Results table
InvDate
InMthsPlus1
28/03/2012
0 (False)
10/12/2012
0 (False)
5/2/2013
0 (False)
31/3/2013
-1 (True)
19/5/2013
0 (False)
15/9/2013
0 (False)
11/12/2013
0 (False)
2/3/2014
0 (False)
14/5/2014
0 (False)
13/6/2014
0 (False)
7/7/2014
0 (False)
4/8/2014
0 (False)
Did this page help you?
If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!
