cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
  • Image to Table JMP extension imports single or batch images as data tables. Useful to extract curves from pictures or convert image data to curve data to use in analyses like Functional Data Explorer. Download and install the extension.

Discussions

Solve problems, and share tips and tricks with other JMP users.
Choose Language Hide Translation Bar
UserID16644
Level V

Loop through dates

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

));
1 REPLY 1
txnelson
Super User

Re: Loop through dates

// 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"
		);
	)
);
Jim

Recommended Articles