Loop through dates

UserID16644 — Thu, 12 Oct 2023 00:05:38 GMT

Hi All,

I am trying pull some data from the database by looping through dates, in this example, I am trying to loop from June until the date yesterday. This is my sample code. However, it is not working. Can someone explain how to properly do it? TIA

d = Today() - In Days(1);
dateNow = Format (d, "m/d/y");
startdate = "2023-06-01";

For( i = startdate, i <= dateNow, i++,

try(
dt = Open Database( "DSN=db ;UID=user123;PWD=qwerty;APP=JMP;DATABASE=zxcv;", 
"Select * from table1
where date_time between '" || [i] || " 00:00:00 AM' and '" || [i] || " 11:59:59 PM'",  
"dt1" );

));

Re: Loop through dates

txnelson — Thu, 12 Oct 2023 01:28:35 GMT

// The Today() function returns the number of seconds since Midnight, January
// 1st, 1904 which includes the number of seconds since midnight today up 
// until the exact h:m:s the today() function was run.
// I assume you really want the value of the start of today.
dateNow = date mdy(month(Today()),day(today()),year(today())) - In Days( 1 );

startdate = Informat( "2023-06-01", "yyyy-mm-dd" );

// Assuming that you want to increment by 1 day, you need to add a
// days worth of seconds for each loop.  JMP dates are stored in
// the number of seconds since Midnight, January 1, 1904.  
For( i = startdate, i <= dateNow, i = i + In Days( 1 ), 

	// When you reference i or datenow or startdate and you don't want
	// the number of seconds, but rather the date value, you need to
	// use the format() function to convert from the number of seconds
	// to the date display form
	Try(
		dt = Open Database(
			"DSN=db ;UID=user123;PWD=qwerty;APP=JMP;DATABASE=zxcv;",
			"Select * from table1
where date_time between '" || Format( i, "m/d/y" ) || " 00:00:00 AM' and '"
			 || Format( i, "m/d/y" ) || " 11:59:59 PM'",
			"dt1"
		);
	)
);

topic Re: Loop through dates in Discussions

Loop through dates

Re: Loop through dates