How to script date of the current month for data compilation

[#1]

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

[#2 · Accepted solution]

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 );

[#3]

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).

[#4]

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 ! 

[#5]

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

[#6]

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;
*/

[#7]

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

[#8 · Accepted solution]

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;
*/

[#9 · Accepted solution]

Hi jthi,

Thanks youn very much, this code

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

works perfectly !

Best regards

[#10]

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