topic Re: How to script date of the current month for data compilation in Discussions

How to script date of the current month for data compilation

Arkawa — Mon, 23 Oct 2023 14:10:19 GMT

Hi all,

I have the following code which does not work :

// Current date
aujourd'hui = Text(Today(),"00000000");

// Obtain the month number format "MM"
numero_mois = Text(Month(aujourd'hui), "00");

// Obtain the year number - 1 format YYYY
numero_annee = Text(Year(aujourd'hui) - 1, "0000");

// Concatenate the result
date_debut_mois_actuel_Y = ("01" || numero_mois || numero_annee);

// Show the result
Show(date_debut_mois_actuel_Y);

dt << Select Where(:Date < date_debut_mois_actuel_Y);
dt << Delete rows;

The objective is to select all rows from dt (Data Table) for which the date is < 01/current month/current year -1, and to delete them.

I understand the error, that I cannot concatenate other elements than character, but how can I obtain the date under the format 01/10/2022 (expecation of today for example), without go back to the code and change manually the date.

The error message is : argument should be character at row xxx in access or evaluation of 'Concat' , Bad Argument( numero_mois ), "01" || /*###*/numero_mois || /*###*/numero_annee/*###*/

Up to date the code is (to be modified manually each month) :

dt << select where(:name("date") < informat("01102022","DDMMYYYY"));
dt << Delete rows;

Thank you very much !

Best regards

Re: How to script date of the current month for data compilation

txnelson — Mon, 23 Oct 2023 14:56:24 GMT

You are making the issue more complex than required. Here is how I would do it:

dt << select where( :Date < Date MDY( 1, Month( today ), Year( Today() ) - 1 ) );
try( dt << delete rows );

Re: How to script date of the current month for data compilation

jthi — Mon, 23 Oct 2023 14:53:22 GMT

You can get the current date using Today() and one option for getting first day of month is using Date Increment()

Names Default To Here(1);
Date Increment(Today(), "Month", 0, "start");

and then depending on your data you can directly use this without performing any Char transforms (in JMP you use Char() not Text() which ChatGPT? tries to use).

Re: How to script date of the current month for data compilation

Arkawa — Tue, 24 Oct 2023 07:51:51 GMT

Hi txnelson and jthi, thanks for your help, the following code is working

dt << select where( :Date < Date DMY( 1, Month( today ), Year( Today() ) - 1 ) );

I just changed the Date MDY to Date DMY.

We learn everyday with JMP :).

Best regards !

Re: How to script date of the current month for data compilation

Arkawa — Mon, 06 Nov 2023 10:24:58 GMT

Dear all,

Today I realized that the code doesn't seem to work when two months overlap, example today : 06/11/2023 - 7 days = 30/10/2023.

I did not find the solution, should I highlight conditions ? (by using "if").

Thanks !

Best regards

Re: How to script date of the current month for data compilation

jthi — Mon, 06 Nov 2023 10:46:46 GMT

I'm not exactly sure what you mean by "two months overlap"? Didn't mean to delete all rows which are earlier than first day of current month last year? Where is the -7 days coming from?

Names Default To Here(1);

dt = New Table("Dates",
	Add Rows(1000),
	New Column("Date", Numeric, Continuous, Format("yyyy-mm-dd", 10), Set Each Value(
		Today() - In Days(Row())
	))
);
dt << Sort(By(:Date), Replace Table, Order(Ascending));
dt << Select Where(
	:Date < Date Increment(Today() - In Years(1), "Month", 0, "start");
);

// dt << select where(:Date < Date DMY(1, Month(Today()), Year(Today()) - 1));

/*
dt << Previous Selected;
*/

Re: How to script date of the current month for data compilation

Arkawa — Mon, 06 Nov 2023 11:13:43 GMT

Hi jthi,

I would like to delete all rows prior to today - 7 days (regardless of the current month and year) :).

Thank you !

Best regards

Re: How to script date of the current month for data compilation

jthi — Mon, 06 Nov 2023 12:58:24 GMT

Here are few options on how you can select those rows. Example table is there so it is easier to see which rows are being selected

Names Default To Here(1);

dt = New Table("Dates",
	Add Rows(1000),
	New Column("Date", Numeric, Continuous, Format("yyyy-mm-dd", 10), Set Each Value(
		Today() - In Days(Row())
	))
);
dt << Sort(By(:Date), Replace Table, Order(Ascending));
dt << Select Where(
	:Date < Date Increment(Today() - In Days(7), "Day", 0, "start");
);

// or using Date Difference
// dt << Select Where(Date Difference(:Date, Today(), "Day") > 7);

// comparison between date and today (depending in your data, removing time of day might be needed)
// dt << Select Where(:Date - Time Of Day(:Date) < Today() - In Days(7) - Time Of Day(Today()));

/*
dt << Previous Selected;
*/

Re: How to script date of the current month for data compilation

Arkawa — Mon, 06 Nov 2023 13:12:38 GMT

Hi jthi,

Thanks youn very much, this code

dt << Select Where(Date Difference(:Date, Today(), "Day") > 7);

works perfectly !

Best regards

Re: How to script date of the current month for data compilation

Kathlyn — Tue, 19 Mar 2024 07:38:11 GMT

Can you explain the process of scripting the date of the current month to ensure accurate data compilation?

Re: How to script date of the current month for data compilation

jthi — Tue, 19 Mar 2024 09:18:41 GMT

If you just want first day of month, use Today() to get the todays date and then Month and Year with Date DMY to build your date

now = Today();
first_of_month = Date DMY(1, Month(now), Year(now));
Show(As Date(first_of_month));