inmonthstodate - script and chart function

This function finds if a timestamp falls within the part a period of the month, bi-month, quarter, tertial, or half-year up to and including the last millisecond of base_date. It is also possible to find if the timestamp falls within a previous or following time period.

Syntax:

InMonths (n_months, timestamp, base_date, period_no[, first_month_of_year ])

Return data type: Boolean

Arguments:

Argument	Description
n_months	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.

Example

Result

inmonthstodate(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 up to the end of 25/04/2013, in which the value of base_date, 25/04/2013 lies.

inmonthstodate(4, '26/04/2013', '25/04/2006', 0)

Returns False. Because 26/04/2013 is outside the same period as the previous example.

inmonthstodate(4, '25/09/2005', '01/02/2006', -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 01/02/2012.

inmonthstodate(4, '25/04/2006', '01/06/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 01/06/2006 instead of 01/05/2006 to 01/06/2006.

Add the example script to your app and run it. Then add, at least, 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 part of the bi-month period up to and including the base_date shifted forwards by four bi-month periods (by specifying period_no as 4).

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 *,

InMonthsToDate(2, InvDate, '15/02/2013', 4) AS InMths2DPlus4

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/09/2013 to 15/10/2013, because the value of base_date is shifted forwards eight months from the value in the function (15/02/2013).

InvDate	InMths2DPlus4
28/03/2012	0 (False)
10/12/2012	0 (False)
5/2/2013	0 (False)
31/3/2013	0 (False)
19/5/2013	0 (False)
15/9/2013	-1 (True)
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!

Leave your feedback here